mysql 编程题库_【SQL】常见的MySQL编程题
Q1：求出每科成绩前三的SQL语句？
问题定义与背景
求出每科成绩前三？
数据表的格式
create table `std_score`(
`sid` int comment 学生id,
`cid` int comment 课程id,
`score` int comment 成绩,
primary key(`sid`, `cid`)
)engine=INNODB;
答案与解析
答案
select * from std_score a
where (select count(*) from std_score b where a.cid=b.cid and b.cid>a.cid) < 3
order by cid,score desc;
解析：嵌套查询，每扫描 a 一条记录都会对比扫描 b 的全部记录。select count(*) from std_score b where a.cid=b.cid and b.cid>a.cid 则是找出比当前扫到 a 的记录的成绩更高的成绩的记录数，where(**)<3 则表示该记录数不能大于3(因为要找出前三的成绩)。
结果展示
结果展示
Q2：删除重复记录并保留一条？
解答过程
1)找出重复记录
select * from xxx_table where [判断重复的字段] in
(select [判断重复的字段] from xxx_table group by [判断重复的字段] having count(*) > 1);
2)删除多余记录，保留其中一条。示例中，保留的是id最小的
delete from xxx_table
where
[判断重复的字段] in (select [判断重复的字段] from xxx_table group by [判断重复的字段] having count(*) > 1)
and
id not in (select min(id) from xxx_table group by [判断重复的字段] having count(*)>1);
知识点
having：分组后(即group by)，对每个组进行聚合。
Q4：性别反转
update person
set sex =
case sex
when m then f
else m
end;
Q4：找出单科成绩高于该科平均成绩的同学名单？
问题定义
问题1：只要有一科满足，该同学就应该在名单中
问题2：名单中的同学必须所有科满足条件。
数据表结构和数据
答案
Q1：求出每科成绩前三的SQL语句？ 问题定义与背景 求出每科成绩前三？ 数据表的格式 create table `std_score`( `sid` int comment 学生id, `cid` int comment 课程id, `score` int comment 成绩, primary key(`sid`, `cid`) )engine=INNODB; 答案与解析 答案 select * from std_score a where (select count(*) from std_score b where a.cid=b.cid and b.cid>a.cid) < 3 order by cid,score desc; 解析：嵌套查询，每扫描 a 一条记录都会对比扫描 b 的全部记录。select count(*) from std_score b where a.cid=b.cid and b.cid>a.cid 则是找出比当前扫到 a 的记录的成绩更高的成绩的记录数，where(**)<3 则表示该记录数不能大于3(因为要找出前三的成绩)。 结果展示 结果展示 Q2：删除重复记录并保留一条？ 解答过程 1)找出重复记录 select * from xxx_table where [判断重复的字段] in (select [判断重复的字段] from xxx_table group by [判断重复的字段] having count(*) > 1); 2)删除多余记录，保留其中一条。示例中，保留的是id最小的 delete from xxx_table where [判断重复的字段] in (select [判断重复的字段] from xxx_table group by [判断重复的字段] having count(*) > 1) and id not in (select min(id) from xxx_table group by [判断重复的字段] having count(*)>1); 知识点 having：分组后(即group by)，对每个组进行聚合。 Q4：性别反转 update person set sex = case sex when m then f else m end; Q4：找出单科成绩高于该科平均成绩的同学名单？ 问题定义 问题1：只要有一科满足，该同学就应该在名单中 问题2：名单中的同学必须所有科满足条件。 数据表结构和数据 答案