Mysql值group by

来源：互联网发布：mysql cast as int 编辑：程序博客网时间：2024/05/22 02:46

mysql中group by默认排序是获取分组的第一条数据

如：

select * from TEST

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

| ID | NAME | ORDER_ID| CREATED_DATE

| 1 | 小红 | 1| 2011-05-31 08:18:52 |
| 2 | 小黄 | 1 | 2011-05-31 09:18:52 |
| 3 | 小绿 | 2 | 2011-05-31 10:18:52 |
| 4 | 小红2 | 2 | 2011-05-31 11:18:52 |
| 5 | 小红3 | 2 | 2011-05-31 12:18:52 |
+----+------------+------------+---------------------+

select * from TEST group by ORDER_ID

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

| ID | NAME | ORDER_ID| CREATED_DATE

| 1 | 小红 | 1| 2011-05-31 08:18:52 |
| 3 | 小绿 | 2 | 2011-05-31 10:18:52 |
+----+------------+------------+---------------------+

但如果目前我希望得到按照ORDER_ID最新的一条记录可以通过如下sql:

select * from (select * from TEST order by ID DESC) as NEW group by ORDER_ID

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

| ID | NAME | ORDER_ID| CREATED_DATE

| 2 | 小黄 | 1 | 2011-05-31 09:18:52 |
| 5| 小红3 | 2 | 2011-05-31 12:18:52 |
+----+------------+------------+---------------------+