mysql date max,mysql – 如何选择max(date)行？

id group date

1 a 2013-01-01

1 b 2014-01-01

2 a 2012-01-01

2 b 2013-01-01

我的数据库是mysql,对于每个id,我需要选择max(date)行,我写了如下SQL：

select id,group, max(date)

from table1

group by id;

但它效果不好.

解决方法:

SELECT B.* FROM

(

select id,max(date) date

from table1 group by id

) A INNER JOIN table1 B USING (id,date);

您应该创建此索引以帮助其更快地运行

ALTER TABLE table1 ADD INDEX (id,date);

加载示例数据

mysql> use test

Database changed

mysql> drop table if exists table1;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table table1

-> (id int not null,

-> `group` varchar(10) not null,

-> date date,

-> key (id,date)

-> );

Query OK, 0 rows affected (0.01 sec)

mysql> insert into table1 (id,`group`,date) values

-> (1,'a','2013-01-01'),

-> (1,'b','2014-01-01'),

-> (2,'a','2012-01-01'),

-> (2,'b','2013-01-01');

Query OK, 4 rows affected (0.00 sec)

Records: 4 Duplicates: 0 Warnings: 0

mysql> select * from table1;

+----+-------+------------+

| id | group | date |

+----+-------+------------+

| 1 | a | 2013-01-01 |

| 1 | b | 2014-01-01 |

| 2 | a | 2012-01-01 |

| 2 | b | 2013-01-01 |

+----+-------+------------+

4 rows in set (0.00 sec)

mysql>

这是执行的查询

mysql> SELECT B.* FROM

-> (

-> select id,max(date) date

-> from table1 group by id

-> ) A INNER JOIN table1 B USING (id,date);

+----+-------+------------+

| id | group | date |

+----+-------+------------+

| 1 | b | 2014-01-01 |

| 2 | b | 2013-01-01 |

+----+-------+------------+

2 rows in set (0.00 sec)

mysql>

试试看 ！！！

标签：mysql,greatest-n-per-group

来源： https://codeday.me/bug/20190806/1597415.html