mysql - 按季度条件从数据库检索数据但应仅基于当年数据

Question

我尝试过这段代码:

它的工作也很好，但问题是，如果当前月份是 2 月并触发此查询，那么它会考虑从现在开始的过去 3 个月，因此从过去的一年开始，即 2012 年 11 月或 12 月说我只想要当年的数据，如果现在是二月，我触发此查询，那么它应该只显示一月和二月的记录。

   SELECT CROEmailId, 
(
SELECT COUNT(LeadId) 
FROM LeadStatus 
WHERE DATE(`LeadTime`)> DATE_SUB(now(), 
INTERVAL 3 MONTH
) 
AND Generated=1 and AssignedTo=a.CROEmailId) 
AS 'NEW LEAD',(
SELECT COUNT(LeadId) 
FROM LeadHistory 
WHERE DATE(UpdatedAt)> DATE_SUB(now(), 
INTERVAL 3 MONTH
) AND AssignedTo=a.CROEmailId) 
AS 'Lead Updated',
(
SELECT SUM(TotalEmails)
FROM MailJobs 
WHERE DATE(CompletedAt)> DATE_SUB(now(), 
INTERVAL 3 MONTH
) 
AND MailFrom=a.CROEmailId) 
AS 'Email Uploaded',
(
SELECT SUM(TotalSent) 
FROM MailJobs 
WHERE DATE(CompletedAt)> DATE_SUB(now(), 
INTERVAL 3 MONTH) 
AND MailFrom=a.CROEmailId
) 
AS 'Email Sent',
(
SELECT SUM(NetTotal) 
FROM Invoice 
WHERE Status='PAID' 
AND DATE(CreatedAt)> DATE_SUB(now(), INTERVAL 3 MONTH) 
AND CROEmailId=a.CROEmailId) 
AS 'Payment Today' FROM CustomersManager a;

Answer 1

尝试改变

DATE_SUB(now(), INTERVAL 3 MONTH)

至

IF(MONTH(CURDATE()) < 4, DATE_FORMAT(CURDATE(), '%Y-01-01'), CURDATE() - INTERVAL 3 MONTH)

在所有子查询中。

SELECT CROEmailId, 
       (SELECT COUNT(LeadId) 
          FROM LeadStatus 
         WHERE DATE(`LeadTime`)> IF(MONTH(CURDATE()) < 4, DATE_FORMAT(CURDATE(), '%Y-01-01'), CURDATE() - INTERVAL 3 MONTH) 
           AND Generated=1 
           AND AssignedTo=a.CROEmailId) AS 'NEW LEAD',
       (SELECT COUNT(LeadId) 
          FROM LeadHistory 
         WHERE DATE(UpdatedAt)> IF(MONTH(CURDATE()) < 4, DATE_FORMAT(CURDATE(), '%Y-01-01'), CURDATE() - INTERVAL 3 MONTH) 
           AND AssignedTo=a.CROEmailId) AS 'Lead Updated',
       (SELECT SUM(TotalEmails)
          from MailJobs 
         WHERE DATE(CompletedAt)> IF(MONTH(CURDATE()) < 4, DATE_FORMAT(CURDATE(), '%Y-01-01'), CURDATE() - INTERVAL 3 MONTH) 
           AND MailFrom=a.CROEmailId) AS 'Email Uploaded',
       (SELECT SUM(TotalSent) 
          FROM MailJobs 
         WHERE DATE(CompletedAt)> IF(MONTH(CURDATE()) < 4, DATE_FORMAT(CURDATE(), '%Y-01-01'), CURDATE() - INTERVAL 3 MONTH) 
           AND MailFrom=a.CROEmailId) AS 'Email Sent',
       (SELECT SUM(NetTotal) 
          FROM Invoice 
         WHERE Status='PAID' 
           AND DATE(CreatedAt)> IF(MONTH(CURDATE()) < 4, DATE_FORMAT(CURDATE(), '%Y-01-01'), CURDATE() - INTERVAL 3 MONTH) 
           AND CROEmailId=a.CROEmailId) AS 'Payment Today' 
  FROM CustomersManager a;