mysql执行时到底是group by先执行还是select先执行

[异常已酿成错误]

在网上搜索了很多回答，都是说group by先执行，但是这个和我的测试结果不一样。所以我不能理解这个问题。先放测试的图
select语句中加入if。

从结果可以看出使用if条件合并了两个查询结果。如果是Group by先执行的话，加上if条件select出来的数据应该是会出错的吧。求大神解答

[crynono]

explain看下执行计划

[学海无涯-回头是岸]

mysql> select * from t1;
+------+------+
| id   | pat  |
+------+------+
|   11 | 11   |
|   11 | 12   |
|   12 | 12   |
|   13 | 12   |
+------+------+
4 rows in set (0.00 sec)

mysql> select (case when id=12 then 13 else id end) idd,sum(pat) from t1 group by  idd;
+------+----------+
| idd  | sum(pat) |
+------+----------+
|   11 |       23 |
|   13 |       24 |
+------+----------+
2 rows in set (0.00 sec)

mysql> EXPLAIN select (case when id=12 then 13 else id end) idd,sum(pat) from t1 group by  idd;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

测试结果是这样，其实很多数据库，不能在group by中用所在表中不存在的列的，这种要用 要把前面的结果，做成派生表。
select idd,sum(pat) from
（select case when id=12 then 13 else id end idd,pat from t1)tt1 group by idd;
这样用，这样有助于理解这个

[异常已酿成错误]

谢谢，我懂了

[异常已酿成错误]

引用 2 楼 baidu_36457652 的回复:

mysql> select * from t1;
+------+------+
| id   | pat  |
+------+------+
|   11 | 11   |
|   11 | 12   |
|   12 | 12   |
|   13 | 12   |
+------+------+
4 rows in set (0.00 sec)

mysql> select (case when id=12 then 13 else id end) idd,sum(pat) from t1 group by  idd;
+------+----------+
| idd  | sum(pat) |
+------+----------+
|   11 |       23 |
|   13 |       24 |
+------+----------+
2 rows in set (0.00 sec)

mysql> EXPLAIN select (case when id=12 then 13 else id end) idd,sum(pat) from t1 group by  idd;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    4 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

测试结果是这样，其实很多数据库，不能在group by中用所在表中不存在的列的，这种要用 要把前面的结果，做成派生表。
select idd,sum(pat) from
（select case when id=12 then 13 else id end idd,pat from t1)tt1 group by idd;
这样用，这样有助于理解这个

谢谢，懂了