mysql - SQL 查询中的问题(预期的输出未出现)

Question

我是 Mysql 的新手。我有一张 Student 表，其列的属性如下:

Result as 'F for fail','P' for pass,
Four department MECH,CSE ECE,EEE,
YEAR as 1,2,3,4 
Mark from 1 to 100
Name as name of student
and first column is auto generated Id

我的预期输出是:

我尝试使用以下方法获取此输出:

 SELECT t1.* FROM(SELECT YEAR,
CASE WHEN DEPT='CSE' THEN COUNT(RESULT) ELSE NULL END CSE ,
CASE WHEN DEPT='ECE' THEN COUNT(RESULT) ELSE NULL END ECE,
CASE WHEN DEPT='MECH' THEN COUNT(RESULT) ELSE NULL END MECh, 
CASE WHEN DEPT='EEE' THEN COUNT(RESULT) ELSE NULL END EEE,
 CASE WHEN DEPT='CIE' THEN COUNT(RESULT) ELSE NULL END CIE
 FROM OT.STUDENT GROUP BY YEAR,DEPT,RESULT) t1

我的输出是:

我卡在这里了。我已经完成了 group by 但为什么 1,2,3,4 会反复出现？1 打印了 8 次，因为只有四年。我想我的查询错了。这是我升序排序时的输出:

Answer 1

我认为您需要条件聚合。这看起来像:

SELECT YEAR,
       SUM(CASE WHEN DEPT = 'CSE' AND RESULT = 'P' THEN 1 ELSE 0 END) as CSE_pass,
       SUM(CASE WHEN DEPT = 'CSE' AND RESULT = 'F' THEN 1 ELSE 0 END) as CSE_fail,
       SUM(CASE WHEN DEPT = 'ECE' AND RESULT = 'P' THEN 1 ELSE 0 END) as ECE_pass,
       . . .
FROM OT.STUDENT
GROUP BY YEAR;

GROUP BY 键定义结果集中的每一行——值的每个组合都是一行。您希望每年一行，因此它应该是 GROUP BY 中的唯一列。