mysql - 如何选择列的每个不同值的最新记录

Question

基于此表：

我如何返回每辆不同车辆的最新记录（假设我知道这些值，但如果有一个解决方案假设我不知道它们会更好）所以让我们说对于这个特定的数据库，它会返回那些在红框（列 id 为 AI）：

我尝试过使用 MAX (id) 但由于某种原因它返回 null

有任何想法吗？

标签： mysqlsql

Answer 1

您可以从您要求的不同值中选择最大 id。如果您的 id 是自动递增的并且最新插入的 id 日期越大，则此示例有效。例如：

create table `car` (
id int(9) not null auto_increment,
`date` varchar(40) default null,
staff varchar(50) default null,
staffindex int(3) default null,
vehicle varchar(50) default null,
vehicleindex int(3) default null,
fuel varchar(30) default null,
km varchar(30) default null,
comments varchar(255) default null,
Primary key id(`id`) );

insert into  car values (1,"26/08/2021","Christos","0","ITY-2683","2","50","128.315",""), (2,"27/08/2021","Sotiris","1","IOY-3949","3","65","322.522","car needs cleaning"), (3,"26/08/2021","Vaggelis","0","ITY-2682","2","50","128.315",""),(4,"26/08/2021","Teo","1","YTI-7963","3","65","322.522","car needs cleaning"),(5,"26/08/2021","Christos","0","ITY-2683","2","50","128.315",""), (6,"27/08/2021","Sotiris","1","IOY-3949","3","65","322.522","car needs cleaning"), (7,"26/08/2021","Vaggelis","0","ITY-2682","2","50","128.315",""),(8,"26/08/2021","Teo","1","YTI-7963","3","65","322.522","car needs cleaning");

select * from car;

对于您的解决方案，您可以使用：

 select * from car where id in (select max(id) from car group by staff) order by id desc;

我正在使用 MariaDB，MySQL 也是如此