sqlserver 中有一成绩表!! 学生id 语文 数学 考试号录入分数时,如果是-1,就表明该学生该科缺考,总分时就不算该科,要求返回有如下几项:学生id 语文 数学 总分 名次 与上次名次对比
原始数据如下::
学生id 语文 数学 考试号
a 85 90 第一次考试
b 69 78 第一次考试
a 66 98 第二次考试
b 58 87 第二次考试请问怎么写sql语句??
原始数据如下::
学生id 语文 数学 考试号
a 85 90 第一次考试
b 69 78 第一次考试
a 66 98 第二次考试
b 58 87 第二次考试请问怎么写sql语句??
GO
CREATE TABLE TB(
STUID VARCHAR(5)
,YUWEN NUMERIC(5,2)
,SHUXUE NUMERIC(5,2)
,KSH VARCHAR(10)
)
INSERT INTO TB
SELECT 'a', 85, 90 ,'第一次考试' UNION ALL
SELECT 'b', 69, 78 ,'第一次考试' UNION ALL
SELECT 'a', 66, 98 ,'第二次考试' UNION ALL
SELECT 'b', 58, 87 ,'第二次考试'SELECT T1.STUID '学生ID',T1.KSH '考试号',T1.YUWEN '语文',T1.SHUXUE '数学'
,CASE WHEN T1.YUWEN>-1 THEN T1.YUWEN ELSE 0 END
+CASE WHEN T1.SHUXUE>-1 THEN T1.SHUXUE ELSE 0 END '总分'
,CASE WHEN T2.KSH IS NOT NULL THEN
CASE WHEN T2.YUWEN>-1 THEN T2.YUWEN ELSE 0 END
+CASE WHEN T2.SHUXUE>-1 THEN T2.SHUXUE ELSE 0 END ELSE NULL END '上次总分'
,RANK() OVER (PARTITION BY T1.KSH ORDER BY CASE WHEN T1.YUWEN>-1 THEN T1.YUWEN ELSE 0 END
+CASE WHEN T1.SHUXUE>-1 THEN T1.SHUXUE ELSE 0 END) '本次排名'
,CASE WHEN T2.KSH IS NOT NULL THEN
RANK() OVER (PARTITION BY T1.KSH ORDER BY CASE WHEN T2.YUWEN>-1 THEN T2.YUWEN ELSE 0 END
+CASE WHEN T2.SHUXUE>-1 THEN T2.SHUXUE ELSE 0 END)
ELSE NULL END '上次排名'
,RANK() OVER (PARTITION BY T1.KSH ORDER BY CASE WHEN T2.YUWEN>-1 THEN T2.YUWEN ELSE 0 END
+CASE WHEN T2.SHUXUE>-1 THEN T2.SHUXUE ELSE 0 END)
-CASE WHEN T2.KSH IS NOT NULL THEN
RANK() OVER (PARTITION BY T1.KSH ORDER BY CASE WHEN T2.YUWEN>-1 THEN T2.YUWEN ELSE 0 END
+CASE WHEN T2.SHUXUE>-1 THEN T2.SHUXUE ELSE 0 END)
ELSE NULL END '与上次名次对比 '
FROM TB T1
LEFT JOIN TB T2 ON T2.STUID=T1.STUID
AND T1.KSH='第二次考试' AND T2.KSH='第一次考试'
ORDER BY T1.STUID,T1.KSH DESC/*
学生ID 考试号 语文 数学 总分 上次总分 本次排名 上次排名 与上次名次对比
----- ---------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- -------------------- -------------------- --------------------
a 第一次考试 85.00 90.00 175.00 NULL 2 NULL NULL
a 第二次考试 66.00 98.00 164.00 175.00 2 2 0
b 第一次考试 69.00 78.00 147.00 NULL 1 NULL NULL
b 第二次考试 58.00 87.00 145.00 147.00 1 1 0*/
学生id 语文 数学 总分 名次 对比第一考试的排名(进步还是退步)a 66 98 164 1 0 (由于第一次考试他也是第一,所以对比为0)
b 58 87 145 2 0