如何将SQL查询统计范围从2024全年改为2024年3月（当月）？

硬编码指定2024年3月的版本 #

Select
    c.Adviser,
    Count(If(Year(l.LeadDate) = '2024' AND Month(l.LeadDate) = '3', 1, Null)) As Leads,
    Count(If(Year(l.signedupdate) = '2024' AND Month(l.signedupdate) = '3', 1, Null)) As Written,
    Count(If(Year(l.CompDate) = '2024' AND Month(l.CompDate) = '3', 1, Null)) As Completed
From
    mortga01_portal.tbl_clients c Inner Join
    mortga01_portal.tbl_lead l On l.client_id = c.client_id
Group By
    c.Adviser

自动适配当前月份的版本（无需手动修改月份） #

如果需要每次查询都自动统计当前系统时间所在月份的数据，可以用CURRENT_DATE函数动态获取年份和月份：

Select
    c.Adviser,
    Count(If(Year(l.LeadDate) = Year(CURRENT_DATE) AND Month(l.LeadDate) = Month(CURRENT_DATE), 1, Null)) As Leads,
    Count(If(Year(l.signedupdate) = Year(CURRENT_DATE) AND Month(l.signedupdate) = Month(CURRENT_DATE), 1, Null)) As Written,
    Count(If(Year(l.CompDate) = Year(CURRENT_DATE) AND Month(l.CompDate) = Month(CURRENT_DATE), 1, Null)) As Completed
From
    mortga01_portal.tbl_clients c Inner Join
    mortga01_portal.tbl_lead l On l.client_id = c.client_id
Group By
    c.Adviser

性能优化提示（可选） #

如果你的日期字段LeadDate、signedupdate、CompDate有索引，用日期范围判断会比Year()+Month()的组合更高效，比如：

Select
    c.Adviser,
    Count(If(l.LeadDate BETWEEN '2024-03-01' AND '2024-03-31 23:59:59', 1, Null)) As Leads,
    Count(If(l.signedupdate BETWEEN '2024-03-01' AND '2024-03-31 23:59:59', 1, Null)) As Written,
    Count(If(l.CompDate BETWEEN '2024-03-01' AND '2024-03-31 23:59:59', 1, Null)) As Completed
From
    mortga01_portal.tbl_clients c Inner Join
    mortga01_portal.tbl_lead l On l.client_id = c.client_id
Group By
    c.Adviser

内容的提问来源于stack exchange，提问作者gary