有表如下,要找到 family_name 为 Green 和 Smith 的身高最高的两个人的 given_name。
+----+-------------+------------+--------+
| id | family_name | given_name | height |
+----+-------------+------------+--------+
| 1 | Smith | Mike | 157 |
| 2 | Smith | Ashlin | 165 |
| 3 | Smith | Eason | 181 |
| 4 | Smith | Kaley | 175 |
| 5 | Green | Jack | 164 |
| 6 | Green | Kate | 179 |
| 7 | Green | Kathryn | 168 |
| 8 | Green | Waller | 187 |
| 9 | Brown | Mary | 178 |
| 10 | Brown | Lana | 173 |
| 11 | Brown | Lange | 175 |
| 12 | Brown | Randy | 177 |
+----+-------------+------------+--------+
在子查询里先按身高排序,再在外面用 family_name 分组:
select given_name from (
SELECT * from height
where family_name in ('Green','Smith')
order by height desc
) b
group by family_name;
结果外层的分组并未按照子查询内的顺序进行。
+------------+
| given_name |
+------------+
| Jack |
| Mike |
+------------+
查询效果和直接查结果一样。
select given_name from height
where family_name in ('Green','Smith')
group by family_name
order by height desc
两句 sql explain 的结果也一样。
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | SIMPLE | height | NULL | ALL | NULL | NULL | NULL | NULL | 12 | 20.00 | Using where; Using temporary; Using filesort |
但是在上述的子查询 sql 中使用 group by,就能达到预期结果。
select given_name from (
SELECT * from height
where family_name in ('Green','Smith')
group by id
order by height desc
) b
group by family_name;
+------------+
| given_name |
+------------+
| Waller |
| Eason |
+------------+
再看 该语句的 explain:
id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---|---|---|---|---|---|---|---|---|---|---|---|
1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | Using temporary; Using filesort |
2 | DERIVED | height | NULL | ALL | PRIMARY | NULL | NULL | NULL | 12 | 20.00 | Using where; Using filesort |
mysql 5.7 版本对子查询进行了优化,子查询会被改为联结查询的形式以节省时间和资源,除非子查询内包含union 、group by、distinct、limit 或使用了聚合函数。
具体原因查看https://blog.csdn.net/luis_ora/article/details/80178931