以下这个语句的执行过程理解上有点困难,请各位前辈解释一下,谢谢.25、查询各科成绩前三名的记录:(不考虑成绩并列情况)
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;
top 3 只能显示前三条记录 ,假如有9个科目,那岂不是有8科目的前三名不能显示,我知道我的理解是错误的,就是不明白,大家解释一下.
SELECT t1.S# as 学生ID,t1.C# as 课程ID,Score as 分数
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#;
top 3 只能显示前三条记录 ,假如有9个科目,那岂不是有8科目的前三名不能显示,我知道我的理解是错误的,就是不明白,大家解释一下.
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#; 要注意红色部分的条件,这样就是每个科目取前三
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC 这里c#就是在告诉你按照不同的c#来取,ORDER BY score DESC 的意思,是取score从大到小。
FROM SC t1
WHERE score IN (SELECT TOP 3 score
FROM SC
WHERE t1.C#= C#
ORDER BY score DESC
)
ORDER BY t1.C#; 要注意红色部分的条件,这样就是每个科目取前三 赞成
T1是嵌套外面的别名 与嵌套里面的C#比较 则代表相同科目
SELECT top 3 t1.S# as 学生ID,t1.C# as 课程ID,St1.core as 分数
FROM SC t1 ,sc b
WHERE t1.c#=b.c#
ORDER BY t1.C#;
你这是最后才执行的TOP 3只能是小于等于3条
FROM C# AS T1
WHERE T1.C# IN(
SELECT C#
FROM SC
GROUP BY C#
)
学习中。。不知道哪个性能更好点
FROM (
SELECT ROW_NUMBER() OVER(PARTITION BY Score ORDER BY Score DESC) AS ScoreRank
FROM SC WHERE ScoreRank IN (1,2,3)
)