如何编写SQL限制查询特定日期范围且无后续服务的记录

需求说明 #

需要统计满足以下条件的工地服务数量：

• 工地在指定日期范围内有符合类型的服务记录
• 该日期范围之后，工地没有任何服务记录

现有SQL仅能查询指定日期后的记录，无法实现上述逻辑；尝试在子查询WHERE中使用MAX()函数时触发错误147：An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference

错误原因 #

SQL语法规则限制：聚合函数（如MAX()、COUNT()）不能直接出现在WHERE子句中——WHERE是在聚合操作前筛选行，而聚合函数是对行集计算后的结果，仅能在HAVING子句（聚合后筛选）或嵌套子查询的SELECT列表中使用。

解决方案 #

通过子查询先获取每个工地的最后服务日期，再基于该日期筛选符合要求的工地，同时统计目标日期范围内的服务数量：

SELECT
    emp.company,
    emp.col_empid,
    emp.siccode,
    empworksite.worksiteid,
    empworksite.location,
    empworksite.city,
    empworksite.zip,
    sp.count_of_services
FROM empworksite
JOIN emp ON emp.col_empid = empworksite.col_empid
JOIN tbl_empxregistration ON tbl_empxregistration.col_empid = empworksite.col_empid
-- 子查询：统计每个工地的符合条件服务数，以及最后服务日期
JOIN (
    SELECT
        col_worksiteid,
        COUNT(col_worksiteid) AS count_of_services,
        MAX(createdate) AS last_service_date
    FROM serviceplan
    WHERE servicecode IN ('EJO','JF','SE','JT')
    GROUP BY col_worksiteid
) sp ON sp.col_worksiteid = empworksite.worksiteid
WHERE 
    empworksite.lwia = '14' 
    AND emp.joborders = '1' 
    AND tbl_empxregistration.col_reg_type_id = '3' 
    AND emp.typeemp = '5' 
    AND emp.siccode NOT LIKE '11%' 
    AND empworksite.active = '1'
    -- 筛选：最后服务日期落在指定范围内（示例为2022-10-01至2023-09-30，可按需调整）
    AND sp.last_service_date BETWEEN '2022-10-01 00:00:00' AND '2023-09-30 23:59:59'
ORDER BY sp.count_of_services DESC;

逻辑说明 #

1. 子查询sp按工地分组，统计符合服务类型的记录数，同时计算该工地的最后服务日期last_service_date
2. 主查询关联子查询，通过last_service_date的范围筛选，确保工地的最后一次服务落在指定区间内（即区间后无新服务）
3. 直接复用子查询统计的服务数量作为结果输出

若你的日期范围仅需“从指定起始日到当前”，可将BETWEEN条件改为sp.last_service_date >= '2022-10-01 00:00:00'即可。

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