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:名单中的同学必须所有科满足条件。
数据表结构和数据
答案