java如何编写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

上面一个是我的数据库表

我想在这里显示这样的表totalworkingdays是基于数据库表的当月的当前和缺席的总和：

 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行的near'as present，SUM（如果attentence='缺席'则为1 ELSE 0 END）用作缺席FRO' （0毫秒）

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

Answer 1

记住总是以更可读的方式放置查询

滚动的需要令人厌恶

这有点混乱

几个FROM和GROUP BY子句

它应该看起来更像

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)

AFAIR您不能将AS放在GROUPBY子句中。这就是MySQL告诉你的

PS.未经测试