如何编写查询以获取基于月份的数据？

Question

我的数据库表是：

attendence date     admission number         attendence

  2013-10-2         LSTM-0008/2013-2014      present

 2013-10-19         LSTM-0008/2013-2014      absent

  2013-9-20         LSTM-0008/2013-2014      present

上面是我的数据库表。

我想根据数据库表显示这样的表：

 MonthName     totalWorkingDays     Present     absent

 october            26                 1          1
 november           26                 1          0

我编写了如下mysql查询：

SELECT DISTINCT monthname(attendencedate)as monthname , COUNT (*) as totalworking days, 
  (SELECT COUNT(*) FROM lstms_attendence WHERE attendence='present' AND addmissionno='LSTM-0008/2013-2014') as present,
  (SELECT COUNT(*) FROM lstms_attendence WHERE attendence='absent' AND addmissionno='LSTM-0008/2013-2014') as absent 
FROM lstms_attendence 
WHERE addmissionno='LSTM-0008/2013-2014'
GROUP BY attendencedate;

这对我没有用，有人给我建议。

Answer 1

试试这个：

SELECT monthname(attendencedate) AS monthname,
  COUNT(*) AS totalworking_days,
  SUM(CASE WHEN attendence = 'present' THEN 1 ELSE 0 END) AS present,
  SUM(CASE WHEN attendence = 'absent' THEN 1 ELSE 0 END AS absent      
FROM lstms_attendence
WHERE addmissionno = 'LSTM-0008/2013-2014'
GROUP BY monthname(attendencedate);

它将对当前列中每一行进行1的求和，否则为0。出席情况相同=“缺席”