SQL查询中的问题（预期的输出未到） [英] Problem in SQL query(Expected output not coming)

问题描述

我是Mysql的新手，我有一个表格Student，其列的属性如下：

 结果为'F表示失败'，'P'为通行证，
四个部门MECH，CSE ECE，EEE，
年份为1,2,3,4 
标记从1到100 
名称为名称学生
和第一列是自动生成的ID 
  

我的预期输出是：

我被困在这里。我按组分组但为什么1,2,3,4会重复出现？由于只有四年，所以1被打印了8次。.我想我的查询错了。这是我升序排序时的输出：

解决方案

我认为您想要有条件的聚合。看起来像这样：

 选择年份，
 SUM（在DEPT ='CSE'并且结果='P'的情况下THEN 1 ELSE 0 END）作为CSE_pass，
 SUM（当DEPT ='CSE'并且结果='F'的情况）THEN 1 ELSE 0 END）作为CSE_fail，
 SUM（当DEPT ='ECE'时的情况） AND RESULT ='P'THEN 1 ELSE 0 END）as ECE_pass，
。 。 。每年来自其他学生的
 
每年分组； 
  

GROUP BY 键定义了每一行结果集-每个值组合都是一行。您每年需要一行，因此应该是 GROUP BY 中的唯一列。

I am new to Mysql.I have a table Student with following atttributes of column:

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

My expected output is:

I tried to get this output using:

 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

My output coming is:

I am stuck here.I have done group by but why 1,2,3,4 is coming repeatedly?The 1 is printed for 8times as there are just four years..I think I m wrong in my query.This is my output when i sorted ascending:

解决方案

I think you want conditional aggregation. This looks like:

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;

The GROUP BY keys define each row in the result set -- each combination of values is one row. You want one row per year, so that should be the only column in the GROUP BY.

这篇关于SQL查询中的问题（预期的输出未到）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！