mysql每月报表的复杂查询

Question

在从家庭或办公室启动新任务时，员工在下表中输入

[tablename=CHECK]
c_id   c_sdate                c_emp       c_task
-------------------------------------------------
1     2013-05-01 01:01:00       1           26    //date 01 from home-----
2     2013-05-01 08:11:00       1           27    //date 01 from office--- Present

3     2013-05-02 03:41:00       1           28    //date 02 from home---
4     2013-05-02 09:12:00       1           29    //date 02 from office-
5     2013-05-02 22:32:00       1           30    //date 02 from home---Present

6     2013-05-03 01:43:00       1           31    //date 03 from home
7     2013-06-03 23:25:00       1           32    //date 03 from home----------Homework

8     2013-06-03 02:15:00       2           33    //other employee

如果有1项或多项记录，在上午8时至晚上8点之间，雇主将被视为在场。

如果有一项或多项记录，而时间不是在上午八时至晚上八时，而没有在该日出现，则雇主将被视为workedFromHome。

注意:如果在上午8点到晚上8点之间有任何记录时间，不将一天计算为workedFromHome (这意味着workedFromHome只有在该日不感到不满时才算在内)。

我想显示一名雇员的月报。月份的c_emp=1 (如。在一个查询中像这样

c_emp  presentCount   HW_Count
  1       3             1   

或单独查询1

c_emp  presentCount  
  1       3

和查询2

c_emp   HW_Count
  1        1     

我试过数一下现在的工作情况。

select count(distinct(date_format(c_sdate,'%e'))) as count 
from ita_check
where date_format(c_sdate,'%m')=5 
and c_emp=1 
and date_format(c_sdate,'%H%i')>=800
and date_format(c_sdate,'%H%i')<=2000

以及计算fromHome的错误计数

select count(distinct(date_format(c_sdate,'%e'))) as count 
from ita_check
where date_format(c_sdate,'%m')=5
and c_eid=1
and c_id not in (
   select c_id 
   from ita_check
   where date_format(c_sdate,'%m')=5 
   and c_eid=1
   and (date_format(c_sdate,'%H%i')<=800 or date_format(c_sdate,'%H%i')>=2000)
)
and date_format(c_sdate,'%H%i')<800
or date_format(c_sdate,'%H%i')>2000

在上面用于计数的查询中，子查询返回1和2，而外部消除c_id=2，而不返回c_id=1。

Answer 1

尝试这个查询

SELECT c_emp, 
sum(if(cnt>=1,1,0)) as Office, 
count(*)-sum(if(cnt>=1,1,0)) as WFH   from (
select c_emp, Date(c_sdate),
sum(if(c_sdate BETWEEN Date(c_sdate) + interval 8 hour 
       AND Date(c_sdate) + interval 20 hour, 1, 0)) as cnt
from table1
group by c_emp, Date(c_sdate)) tmp
group by c_emp

SQL小提琴

| C_EMP | OFFICE | WFH |
------------------------
|     1 |      2 |   2 |
|     2 |      0 |   1 |

月报

SELECT c_emp, date_format(c_date, '%c %Y') as Mnth,
sum(if(cnt>=1,1,0)) as Office, 
count(*)-sum(if(cnt>=1,1,0)) as WFH   from (
select c_emp, Date(c_sdate) as c_date,
sum(if(c_sdate BETWEEN Date(c_sdate) + interval 8 hour 
       AND Date(c_sdate) + interval 20 hour, 1, 0)) as cnt
from table1
group by c_emp, Date(c_sdate)) tmp
group by c_emp,Mnth 

SQL小提琴

| C_EMP |   MNTH | OFFICE | WFH |
---------------------------------
|     1 | 5 2013 |      2 |   1 |
|     1 | 6 2013 |      0 |   1 |
|     2 | 6 2013 |      0 |   1 |