我无法在 MySQL 中获取前几个月的数据

Question

我有这个 MySQL 查询来根据我的选择显示当前月份和前 2 个月。例如，我在日期选择器中选择日期范围2011-10-01到2011-10-31（十月），它将显示十月和前两个月。我有这个 SQL 查询，但它只显示当月（十月）的数据，而不显示上个月的数据。

SELECT jp.JO_partner_ID,ss.first_name as SS, ssa.first_name as SSA 
,count(CASE WHEN jp.receivedDate between DATE_SUB('2011-10-01', INTERVAL 2 MONTH) 
and DATE_SUB('2011-10-31', INTERVAL 2 MONTH) THEN jp.job_order_number ELSE null END) As aug 
,count(CASE WHEN jp.receivedDate between DATE_SUB('2011-10-01', INTERVAL 1 MONTH) 
and DATE_SUB('2011-10-31', INTERVAL 1 MONTH) THEN jp.job_order_number ELSE null END) As sep 
,count(CASE WHEN jp.receivedDate between '2011-10-01' 
and '2011-10-31' THEN jp.job_order_number ELSE null END) As oct 
,count(case WHEN (jp.receivedDate between '2011-10-01'and '2011-10-31' and      jo.job_order_type LIKE 'IT') then jp.job_order_number else null end) as IT 
,count(case WHEN (jp.receivedDate between  '2011-10-01' and '2011-10-31' and jo.job_order_type LIKE 'Non-IT') then jp.job_order_number else null end) as NonIT 
,count(jp.job_order_number) As Total FROM jo_partner jp 
left join specialist_partner sp on jp.JO_partner_ID = sp.specialistPartnerID 
left join staffing_specialist_asst ssa on jp.SSA_ID = ssa.SSA_ID 
left join staffing_specialist ss on jp.SS_ID = ss.SS_ID 
left join job_order jo on jp.job_order_number = jo.job_order_number 
left join candidate_jo cjo on jp.JO_partner_ID= cjo.jo_partner_ID 
left join candidate can on cjo.candidate_jo_ID= can.candidate_ID 
WHERE jp.receivedDate BETWEEN '2011-10-01' AND '2011-10-31'
GROUP BY ss.SS_ID; 

这是我的结果的屏幕截图： http://www.fileden.com/files/2011/ 7/27/3174077//3.JPG

任何人都可以帮助纠正我的 SQL 或简化吗？？？谢谢...

Answer 1

问题出在您的 WHERE 子句中，您将 jp.receivedDate 限制在 '2011-10-01' AND '2011-10-31' 之间。基本上，您限制在 2011 年 10 月 jp.receivedDate 中具有值的记录。

尝试：

where jp.receivedDate Between date_sub('2011-10-01', Interval 2 Month)和“2011-10-31”