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 |
+----+------------+------------+---------------------+