MySQL - (面试题)查询每个主播的最大level以及对应的最小gap

查询每个主播的最⼤level以及对应的最⼩gap(注意:不是每个主播的最⼤level和最⼩gap)

zhubo_id	level	gap
123	8	20
123	9	30
123	9	40
246	6	20
246	6	30

键表语句

CREATE TABLE `mst_zhubo` (
 `zhubo_id` int(11) NOT NULL,
 `level` int(255) DEFAULT NULL,
 `gap` int(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `mst_zhubo` VALUES (123, 8, 20);
INSERT INTO `mst_zhubo` VALUES (123, 9, 40);
INSERT INTO `mst_zhubo` VALUES (123, 9, 30);
INSERT INTO `mst_zhubo` VALUES (246, 6, 30);
INSERT INTO `mst_zhubo` VALUES (246, 6, 20);

理应得到的结果是

zhubo_id	level	gap
123	9	30
246	6	20

分析：

-- 1.查最大level对应zhubo_id
select zhubo_id,max(level) max_level from mst_zhubo group by zhubo_id;
+----------+------------+
| zhubo_id | max(level) |
+----------+------------+
|      123 |          9 |
|      246 |          6 |
+----------+------------+
2 rows in set (0.00 sec)

--2.利用最大level，zhubo_id与mst_zhubo表联结，条件是level,zhubo_id相等，且gap最小
select s1.zhubo_id,s1.max_level,min(s2.gap)
from (select zhubo_id,max(level) max_level from mst_zhubo group by zhubo_id) s1
join mst_zhubo s2
on s1.zhubo_id = s2.zhubo_id and s1.max_level = s2.level
group by s1.zhubo_id,s1.max_level;
+----------+-----------+-------------+
| zhubo_id | max_level | min(s2.gap) |
+----------+-----------+-------------+
|      123 |         9 |          30 |
|      246 |         6 |          20 |
+----------+-----------+-------------+
2 rows in set (0.00 sec)

--3.这里使用优化一下sql语句，使用in()
select zhubo_id,level,min(gap)
from mst_zhubo where (zhubo_id,level) in (select zhubo_id,max(level) from mst_zhubo group by zhubo_id)
group by zhubo_id,level;
+----------+-------+----------+
| zhubo_id | level | min(gap) |
+----------+-------+----------+
|      123 |     9 |       30 |
|      246 |     6 |       20 |
+----------+-------+----------+
2 rows in set (0.01 sec)
# 可见效果是一样的，关键在于后者使用in()极大的简化了sql语句!