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：名单中的同学必须所有科满足条件。

数据表结构和数据

答案