写本篇前 首先非常感谢的两位CSDN上的朋友是:
echiynn(寶琲)和
zhazhaok(喳喳)
本人工作中遇到一张比较复杂的表,数据大约几百万条,由于涉及到工作机密,暂时以下面的数据来说明我要处理的问题。
数据库中scorenew表中有如下数据:记录了一个学校比较乱的学生成绩记录,每个学生对应不同的课程,一个课程又至少有两个老师,在2006年里考试不及格的同学(result='F'),可在2007年补考,若补考过了则该学生该门课程是认为合格的(如2002101),也可以在2006考试虽然是及格,但由于分数刚好60,也允许同学补考,但补考成绩不及格则被认为该生课程是不合格的(如2002102也就是对同一个学生ID,课程成绩以该门课程考试时间最大的那个为准).
学号 课程名 老师 考试结果 考试日期 班级
studentid classname classteacher result testdate banji
2002100 数学 B2老师 P 2006-7-3 1
2002100 外语 C1老师 P 2006-7-5 1
2002100 语文 A1老师 P 2006-7-1 1
2002101 数学 B2老师 P 2006-7-3 1
2002101 语文 A1老师 F 2006-7-1 1
2002101 语文 A1老师 P 2007-7-1 1
2002102 数学 B1老师 P 2006-7-3 2
2002102 数学 B1老师 F 2007-7-3 2
2002102 外语 C1老师 P 2006-7-5 2
2002102 语文 A1老师 F 2006-7-1 2
2002102 语文 A1老师 F 2007-7-1 2
2002103 数学 B1老师 P 2006-7-3 2
2002103 外语 C2老师 P 2006-7-5 2
2002103 语文 A2老师 P 2007-7-1 2
2002104 数学 B2老师 P 2006-7-3 1
2002104 外语 C1老师 F 2006-7-5 1
2002104 外语 C1老师 P 2007-7-5 1
2002104 语文 A2老师 P 2006-7-1 1
.现在求下面几个SQL语句:
1.求所有科目都及格的人数
1.1.先求出总人数
SELECT COUNT(DISTINCT studentid) AS 总人数 FROM scorenew
结果=5
1.2.再求出考试科目最终有不及格的个数。
SELECT STUDENTID, Result
FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate
FROM SCORENEW t
WHERE NOT EXISTS
(SELECT 1
FROM SCORENEW
WHERE STUDENTID = t .STUDENTID AND
CLASSNAME = t .CLASSNAME AND TestDate > t .TestDate)) a
GROUP BY STUDENTID, Result
HAVING (Result = 'F')
结果显示
2002102 F
改成如下语句
SELECT COUNT(DISTINCT STUDENTID) AS 不及格人数
FROM (SELECT STUDENTID, Result
FROM (SELECT STUDENTID, CLASSNAME, Result, TestDate
FROM SCORENEW t