mysql group by 和order by 执行顺序

最新推荐文章于 2024-04-13 14:45:34 发布

iteye_18850

最新推荐文章于 2024-04-13 14:45:34 发布

阅读量977

点赞数

分类专栏： mysql 文章标签： MySQL SQL

本文链接：https://blog.csdn.net/iteye_18850/article/details/81953941

版权

mysql 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

Mysql 中group by 和 order by的顺序问题：

1: select historyId ,userId, date from history group by userId order by historyId desc;
以上1代码查询的结果是这样的：
+-----------+--------+---------------------+
| historyId | userId | date |
+-----------+--------+---------------------+
| 317 | 139 | 2010-08-28 18:12:11 |
| 310 | 138 | 2010-08-28 17:35:52 |
| 306 | 137 | 2010-08-28 17:30:20 |
| 303 | 135 | 2010-08-28 17:25:20 |
| 292 | 131 | 2010-08-27 17:43:43 |
| 274 | 38 | 2010-08-26 11:42:28 |
| 258 | 100 | 2010-08-25 15:01:46 |
| 249 | 2 | 2010-08-20 18:37:02 |
| 233 | 10 | 2010-08-23 17:58:42 |
| 162 | 31 | 2010-08-19 16:56:09 |
| 151 | 39 | 2010-08-19 15:48:33 |
| 124 | 1 | 2010-08-19 10:01:24 |
+-----------+--------+---------------------+
说明是先group by user_id 然后再 order by historyId 的。那么怎样让程序先执行order by historyId ?

这样可以行的通：

2;select historyId ,userId,date from (select * from history order by historyId desc) c group by userId order by historyId desc;
以上代码2查询的结果是这样的：
+-----------+--------+---------------------+
| historyId | userId | date |
+-----------+--------+---------------------+
| 394 | 1 | 2010-09-03 14:45:30 |
| 386 | 100 | 2010-09-03 11:39:08 |
| 381 | 2 | 2010-09-02 19:43:33 |
| 335 | 39 | 2010-08-31 15:28:27 |
| 318 | 139 | 2010-08-28 18:12:53 |
| 316 | 138 | 2010-08-28 18:10:40 |
| 309 | 137 | 2010-08-28 17:31:02 |
| 305 | 135 | 2010-08-28 17:26:23 |
| 296 | 131 | 2010-08-27 17:55:41 |
| 284 | 38 | 2010-08-26 16:46:06 |
| 233 | 10 | 2010-08-23 17:58:42 |
| 218 | 31 | 2010-08-23 15:46:02 |
+-----------+--------+---------------------+
很明显：代码2执行的结果告诉我们查询的顺序是先通过order by 排序，在通过group by 排序，最后通过 order 对这个查询结果进行一个整体的排序。