05-数据库_数据库⾼级_SQL进阶⾯试题⽬录
六，SQL进阶⾯试题
1，⼀条SQL语句查询出所有成绩⼤于80的学⽣姓名
建表语句
-- 建表语句
CREATE TABLE `mst_stu` (
`name` varchar(255) DEFAULT NULL,
`course` varchar(255) DEFAULT NULL,
`score` int(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- 数据
INSERT INTO `mst_stu` VALUES ('张三', '语⽂', 81);
INSERT INTO `mst_stu` VALUES ('张三', '数学', 75);
INSERT INTO `mst_stu` VALUES ('李四', '语⽂', 76);
INSERT INTO `mst_stu` VALUES ('李四', '数学', 90);
INSERT INTO `mst_stu` VALUES ('王五', '语⽂', 81);
INSERT INTO `mst_stu` VALUES ('王五', '数学', 100);
INSERT INTO `mst_stu` VALUES ('王五', '英语', 90);
解题思路
根据学员进⾏分组，最低分⾼于80即符合要求
-- 先根据学员进⾏分组，看每个⼈的最低分
select name,min(score) from mst_stu group by name;
robotframework接口测试-- 在使⽤分组过滤 having 筛选出最低分⼤于80
select name,min(score) as min_sc from mst_stu group by name having min_sc > 80;
-
- 最终只需要符合要求的学员姓名
select name from mst_stu group by name having min(score) > 80;
2，查询后⼀天 temperature ⽐前⼀天⾼的date
查与之前（昨天的）⽇期相⽐温度更⾼的所有⽇期的 Id。
建表语句
-- mst_Weathermysql面试题sql语句多表联查
CREATE TABLE `mst_weather` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`date` date DEFAULT NULL,
`temperature` int(255) DEFAULT NULL,
PRIMARY KEY (`id`)
)
horizontal alignment ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4;
INSERT INTO `mst_weather` VALUES (1, '2022-04-01', 20);
INSERT INTO `mst_weather` VALUES (2, '2022-04-02', 25);
INSERT INTO `mst_weather` VALUES (3, '2022-04-03', 21);
INSERT INTO `mst_weather` VALUES (4, '2022-04-04', 24);
解题思路
两个相同的表做⾃联结，联结条件是s1表的⽇期与s2表⽇期之差为1，且s1表的温度⾼于s2表的温度datediff⽤于计算两个⽇期之差
select s1.id
from mst_weather as s1
join mst_weather as s2
on datediff(s1.date,s2.date) = 1
perature > s2.temperature;
3，查询每个主播的最⼤level以及对应的最⼩gap(注意：不是每个主播的最⼤level和最⼩gap)⾸先锁定最⼤的level，其次挑选最⼩的gap
建表语句
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);activity反义词
INSERT INTO `mst_zhubo` VALUES (123, 9, 30);
INSERT INTO `mst_zhubo` VALUES (246, 6, 30);
INSERT INTO `mst_zhubo` VALUES (246, 6, 20);
解题思路
-- 先查询每个主播的最⼤ level
select zhubo_id,max(level) from mst_zhubo group by zhubo_id;
+----------+------------+
| zhubo_id | max(level) |
+----------+------------+
| 123 | 9 |
| 246 | 6 |
+----------+------------+
2 rows in set (0.00 sec)
--在这个基础上，查询出每个主播所有符合最⼤level的数据
select * from mst_zhubo where (zhubo_id,level) in (select zhubo_id,max(level) from mst_zhubo group by zhubo_id)
+----------+-------+------+
| zhubo_id | level | gap |
+----------+-------+------+
| 123 | 9 | 40 |
| 123 | 9 | 30 |
| 246 | 6 | 30 |
| 246 | 6 | 20 |
+----------+-------+------+
4 rows in set (0.01 sec)
-- 在这个基础上，按照主播分组，求最⼩的gap
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)
4，课程数据⾏转列
建表语句
CREATE TABLE `mst_class` (
`class_id` varchar(255) NOT NULL,
there的同音词`grade` varchar(255) DEFAULT NULL,
`rate` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT INTO `mst_class` VALUES ('abc123', 'primary', '70%'); INSERT INTO `mst_class` VALUES ('abc123', 'middle', '65%');
INSERT INTO `mst_class` VALUES ('abc123', 'high', '72%');
INSERT INTO `mst_class` VALUES ('hjkk86', 'primary', '69%');
INSERT INTO `mst_class` VALUES ('hjkk86', 'middle', '63%');
INSERT INTO `mst_class` VALUES ('hjkk86', 'high', '74%');
解题思路
使⽤case-when / if-else语句选择各列的值，并取最⼤值进⾏替换
--CASE WHEN
select class_id,
max(CASE WHEN grade = 'primary' THEN rate ELSE 0 END) as 'primary', max(CASE WHEN grade = 'middle' THEN rate ELSE 0 END) as 'middle', max(CASE WHEN grade = 'high' THEN rate ELSE 0 END) as 'high'
from mst_class
group by class_id;
--IF
select class_id,
max(IF(grade = 'primary',rate,0)) as 'primary',
max(IF(grade = 'middle',rate,0)) as 'middle',
tcp ip协议在哪里设置max(IF(grade = 'high',rate,0)) as 'high'
from mst_class
group by class_id;
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论