我非常感谢一些帮助创建查询的帮助,该查询对多个列执行计数,然后将数据插入到一个新表中。有一个表,根据阅卷时间存储每个学生的成绩。我已经写了一个查询来计算学生在Mark 1中收到的D或F的数量。现在我想对Mark 2运行相同的计算。以下是Mark 1代码的结果:
SELECT STU.ID, COUNT(GRD.M1) as DF_M1
INTO LCAPDEMO
FROM STU JOIN
GRD ON STU.SN = GRD.SN AND STU.SC = GRD.SC
WHERE GRD.M1 IN ('D+','D','D-','F+','F','F-')
GROUP BY STU.ID
对于Mark 1和Mark 2年级,每天晚上都需要运行此处理。
发布于 2015-11-05 06:03:22
SELECT STU.ID,
SUM(CASE WHEN GRD.M1 IN ('D+', 'D', 'D-', 'F+', 'F', 'F-')
THEN 1 ELSE 0 END) as DF_M1,
SUM(CASE WHEN GRD.M2 IN ('D+', 'D', 'D-', 'F+', 'F', 'F-')
THEN 1 ELSE 0 END) as DF_M2
INTO LCAPDEMO
FROM STU JOIN
GRD
ON STU.SN = GRD.SN AND STU.SC = GRD.SC
GROUP BY STU.ID;
https://stackoverflow.com/questions/33532855
复制相似问题