第9题：
select sid from sc where sid<>'02' group by sid
having group_concat(cid) = (select group_concat(cid) from SC where sid = '02')
发简信
IP属地：上海
-
-
35题，我是参考作者18题写的。我不知道对不对，请各位指点。SELECT * FROM sc WHERE
(SELECT COUNT(*) FROM sc a
WHERE sc.SId != a.sid AND sc.score = a.score)> 2
ORDER BY sc.CId,sc.score DESC;50道SQL练习题及答案与详细分析
网上流传较广的50道SQL训练，奋斗了不知道多久终于写完了。前18道题的难度依次递增，从19题开始的后半部分算是循环练习和额外function的附加练习，难度恢复到普通状态。...
-
谢谢你的答案，
50道SQL练习题及答案与详细分析
网上流传较广的50道SQL训练，奋斗了不知道多久终于写完了。前18道题的难度依次递增，从19题开始的后半部分算是循环练习和额外function的附加练习，难度恢复到普通状态。...
-
15、15.1、16、16.1、18可以用：
rank() over (partition by 分组属性 order by 排序属性) --不合并名次
及
dense_rank() over (partition by 分组属性 order by 排序属性) --合并名次
15
select CId, SId, score, rank() over (partition by CId order by score desc) as Num
from SC;
15.1
select CId, SId, score, dense_rank() over (partition by CId order by score desc) as Num
from SC;
16
select SId, sum(score) as sum_score, rank() over(order by sum(score) desc) as Num
from SC
group by SId;
16.1
select SId, sum(score) as sum_score, dense_rank() over(order by sum(score) desc) as Num
from SC
group by SId;
18
select *
from(select CId, SId, score, dense_rank() over (partition by CId order by score desc) as Num from SC) as A
where Num <=3; -
第17题：
SELECT course.Cname, c.* FROM course,
(SELECT sc.CId,
SUM(CASE WHEN sc.score >= 85 THEN 1 ELSE 0 END)/COUNT(sc.CId) '100-85的百分比',
SUM(CASE WHEN sc.score >= 85 THEN 1 ELSE 0 END) '100-85',
SUM(CASE WHEN sc.score < 85 AND sc.CId >= 70 THEN 1 ELSE 0 END)/COUNT(sc.CId) '85-70的百分比',
SUM(CASE WHEN sc.score < 85 AND sc.CId >= 70 THEN 1 ELSE 0 END) '85-70',
SUM(CASE WHEN sc.score < 70 AND sc.CId >= 60 THEN 1 ELSE 0 END)/COUNT(sc.CId) '70-60的百分比',
SUM(CASE WHEN sc.score < 70 AND sc.CId >= 60 THEN 1 ELSE 0 END) '70-60',
SUM(CASE WHEN sc.score < 60 THEN 1 ELSE 0 END)/COUNT(sc.CId) '60以下的百分比',
SUM(CASE WHEN sc.score < 60 THEN 1 ELSE 0 END) '60以下'
FROM sc
GROUP BY sc.CId) c
WHERE course.CId = c.CId;50道SQL练习题及答案与详细分析
网上流传较广的50道SQL训练，奋斗了不知道多久终于写完了。前18道题的难度依次递增，从19题开始的后半部分算是循环练习和额外function的附加练习，难度恢复到普通状态。...