mysql - 需要缺席和出席人数以及月份名称

Question

我需要一个带有缺席和在场计数的月份名称。这是我的数据库查询:

SELECT sid,COUNT(CASE WHEN STATUS ='A' THEN 1 END) AS absent_count,COUNT(CASE WHEN STATUS ='P' THEN 1 END) AS present_count,
      MONTHNAME(attendance_date) AS `Month_Name`
      FROM attendance
      WHERE SID = '2'
      AND campus_id = 2
      GROUP BY sid;

Answer 1

group by sid 没有意义 - 根据您的 where 子句，它始终是 '2'。相反，由于您要计算每个月的名称，因此它应该出现在 group by 子句中:

SELECT   MONTHNAME(attendance_date) AS `Month_Name`, 
         COUNT(CASE WHEN STATUS ='A' THEN 1 END) AS absent_count,
         COUNT(CASE WHEN STATUS ='P' THEN 1 END) AS present_count,
FROM     attendance
WHERE    sid = '2' AND campus_id = 2
GROUP BY MONTHNAME(attendance_date);