p; T2.score AS 马克思,
T3.score AS UML,
T4.score AS 数据库,
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) as 总分
FROM Student,SC LEFT JOIN SC AS T1
ON SC.S# = T1.S# AND T1.C# = ''001''
LEFT JOIN SC AS T2
ON SC.S# = T2.S# AND T2.C# = ''002''
LEFT JOIN SC AS T3
ON SC.S# = T3.S# AND T3.C# = ''003''
LEFT JOIN SC AS T4
ON SC.S# = T4.S# AND T4.C# = ''004''
WHERE student.S#=SC.S# and
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
NOT IN
(SELECT
DISTINCT
TOP 15 WITH TIES
ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0)
FROM sc
LEFT JOIN sc AS T1
ON sc.S# = T1.S# AND T1.C# = ''k1''
LEFT JOIN sc AS T2
ON sc.S# = T2.S# AND T2.C# = ''k2''
LEFT JOIN sc AS T3
ON sc.S# = T3.S# AND T3.C# = ''k3''
LEFT JOIN sc AS T4
ON sc.S# = T4.S# AND T4.C# = ''k4''
ORDER BY ISNULL(T1.score,0) + ISNULL(T2.score,0) + ISNULL(T3.score,0) + ISNULL(T4.score,0) DESC);
23、统计列印各科成绩,各分数段人数:课程I
