MySQL查询错误...它不显示确切的数据值

Question

我想问一下我的 MySQL 查询是否出了什么问题（可能还需要寻求如何更好地做到这一点的建议）？我正在尝试生成 5 天范围内的每日报告...问题是它不显示确切的数据，它填满了没有数据的日期。

下面的 MySQL 查询示例就是这种情况： 在我的数据库中，星期一、星期三和星期四包含数据。但是如果我使用此查询运行报告，星期二将有一个现有数据，但实际上并不存在我的数据库中有。尽管IT、NonIt 和Total 表计数是正确的。我仅在计算天内的数据时出现错误。请帮助我...我已经不知道错误在哪里了。

为了更好地解释我的意思，这是我的查询输出结果的屏幕截图： http://www.fileden.com/files/2011/ 7/27/3174077//每日.JPG

SELECT a.specialist_partner_ID, ss.first_name as SS, ssa.first_name as SSA 

,count(CASE WHEN a.receivedDate BETWEEN '2011-09-5' AND '2011-09-9' 
THEN a.job_order_number ELSE null END) As MON

,count(CASE WHEN a.receivedDate BETWEEN date_add('2011-09-5', INTERVAL 1 DAY) 
AND DATE_ADD('2011-09-9', INTERVAL 1 DAY) THEN a.job_order_number ELSE null END) As TUE 

,count(CASE WHEN a.receivedDate BETWEEN date_add('2011-09-5', INTERVAL 2 DAY) 
AND DATE_ADD('2011-09-9', INTERVAL 2 DAY) THEN a.job_order_number ELSE null END) As WED

,count(CASE WHEN a.receivedDate BETWEEN date_add('2011-09-5', INTERVAL 3 DAY)
AND DATE_ADD('2011-09-9', INTERVAL 3 DAY) THEN a.job_order_number ELSE null END) As THU 

,count(CASE WHEN a.receivedDate BETWEEN date_add('2011-09-5', INTERVAL 4 DAY) 
AND DATE_ADD('2011-09-9', INTERVAL 4 DAY) THEN a.job_order_number ELSE null END) As FRI

,count(case WHEN (a.receivedDate between '2011-09-5 00:00:00' and '2011-09-9 23:59:59' 
and jo.job_order_type LIKE 'IT') then a.job_order_number else null end) as IT 

,count(case WHEN (a.receivedDate between '2011-09-5 00:00:00' and '2011-09-9 23:59:59' 
and jo.job_order_type LIKE 'Non-IT') then a.job_order_number else null end) as NonIT 

,count(a.job_order_number) As Total FROM jo_partner a 

left join specialist_partner sp on a.specialist_Partner_ID = sp.specialistPartnerID       
left join staffing_specialist_asst ssa on sp.SSA_ID = ssa.SSA_ID 
left join staffing_specialist ss on sp.SS_ID = ss.SS_ID 
left join job_order jo on a.job_order_number = jo.job_order_number 
left join candidate_jo cjo on a.JO_partner_ID= cjo.jo_partner_ID 
left join candidate can on cjo.candidate_jo_ID= can.candidate_ID 
WHERE a.receivedDate BETWEEN '2011-09-5 00:00:00' AND '2011-09-9 23:59:59' 
GROUP BY a.specialist_partner_ID

Answer 1

我认为你的日期有误。对于每天的列，您选择 5 天的数据，这是没有意义的。
因此，

count(CASE WHEN a.receivedDate BETWEEN date_add('2011-09-5', INTERVAL 1 DAY) 
AND DATE_ADD('2011-09-9', INTERVAL 1 DAY) THEN a.job_order_number ELSE null END) As TUE

您应该：

count(CASE WHEN a.receivedDate BETWEEN date_add('2011-09-5', INTERVAL 1 DAY) 
and DATE_ADD('2011-09-5', INTERVAL 2 DAY) THEN a.job_order_number ELSE null END) As TUE

或者更好的是：

count(CASE WHEN a.receivedDate >= date_add('2011-09-5', INTERVAL 1 DAY) 
and a.receivedDate < DATE_ADD('2011-09-5', INTERVAL 2 DAY) THEN a.job_order_number ELSE null END) As TUE

其余的日子都一样。