如何编写Mysql查询，根据日期从数据库中获取数据？

Question

我的数据库表是：

attendence date     admission number         attendence

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

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

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

上面是我的数据库表。

我想在这里显示这样的表格，根据数据库表，总工作日是那个月的现职和缺勤日期：

 MonthName     totalWorkingDays     Present     absent

 october            3                  2          1

我编写了如下mysql查询：

select distinct
    monthname(attendencedate) as monthname,
    count(*) as totalworkingdays
from  lstms_attendence where
    attendencedate> '2013-10-01' and attendencedate<'2013-10-31'
    and addmissionno='LSTM-0008/2013-2014'
GROUP BY
    monthname(attendencedate),
    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);

我的错误是：

错误代码: 1064您的SQL语法有错误；请检查与您的MySQL服务器版本对应的手册，以获得正确的语法，以便在第1行(取0毫秒)中使用接近当前的和和(如果乘务员=“缺席”则为1其他0结尾)。

有人给我建议。提前谢谢。

Answer 1

请记住，查询总是以更易读的方式进行。

卷轴的需求令人厌恶。

有点搞混了

有几个从句.

它看起来应该更像

SELECT DISTINCT
    monthname(attendencedate) as monthname,
    count(*) as totalworkingdays,
    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
    attendencedate> '2013-10-01' and attendencedate<'2013-10-31'
    and addmissionno='LSTM-0008/2013-2014'
GROUP BY
    monthname(attendencedate)

请不要将AS按子句分组。这就是MySQL告诉你的。

PS。未测试