SQL Server 2019：统计指定条件下location_id未出现次数

我有两张表：

• _app_locations：以location_id为索引，存储地点信息
• _app_signatures：包含signature_date、location_id、signature_label_id字段

需要编写SQL（SQL Server 2019环境），统计signature_date在指定起止日期内且signature_label_id=1的条件下，_app_locations中每个location_id未出现在_app_signatures中的次数。

表结构与示例数据 #

_app_locations表 #

_app_signatures表数据 #

预期结果 #

当统计日期范围为2024-03-01至2024-03-04，且signature_label_id=1时：

• Location 1: 2次
• Location 2: 0次
• Location 3: 0次
• Location 4: 2次

尝试的错误SQL #

我写了以下代码，但未返回任何行：

declare @location_id int = 2
declare @sdate as date = '3/1/2024'
declare @enddate as date = '3/30/2024'

select a.location_id
from _app_locations a
where a.location_id not in (
    select distinct location_id
    from _app_signatures b
    where cast(signature_date as date) >= @sdate
    and cast(signature_date as date) <= @enddate
    and signature_label_id = 1
    and location_id = @location_id
)
and a.location_id = @location_id

解决方案 #

错误原因 #

1. 固定@location_id=2，只能查询单个地点，无法统计所有地点
2. 逻辑矛盾：子查询里限定了location_id=@location_id，若该地点有符合条件的记录，外层not in会排除它；若没有符合条件的记录，子查询返回空，not in处理空值时会直接返回空结果集

正确SQL代码 #

DECLARE @sdate DATE = '2024-03-01',
        @enddate DATE = '2024-03-04';

-- 生成日期范围内的所有日期
WITH DateRange AS (
    SELECT @sdate AS dt
    UNION ALL
    SELECT DATEADD(DAY, 1, dt)
    FROM DateRange
    WHERE dt < @enddate
),
-- 所有地点+日期的组合
LocationDate AS (
    SELECT l.location_id, l.location, dr.dt
    FROM _app_locations l
    CROSS JOIN DateRange dr
),
-- 符合条件的已签到记录（去重，避免同一天同一地点多条记录干扰）
ValidSignatures AS (
    SELECT DISTINCT location_id, CAST(signature_date AS DATE) AS dt
    FROM _app_signatures
    WHERE CAST(signature_date AS DATE) BETWEEN @sdate AND @enddate
      AND signature_label_id = 1
)
SELECT 
    ld.location,
    COUNT(*) AS 未出现次数
FROM LocationDate ld
LEFT JOIN ValidSignatures vs 
    ON ld.location_id = vs.location_id AND ld.dt = vs.dt
WHERE vs.location_id IS NULL
GROUP BY ld.location_id, ld.location
ORDER BY ld.location_id;

代码说明 #

1. DateRange：递归生成指定日期范围内的每一天
2. LocationDate：将所有地点与日期做交叉连接，得到每个地点在统计期内的所有预期签到日期
3. ValidSignatures：筛选出符合条件的签到记录并去重，确保每个地点每天只算一次有效签到
4. 左连接找出未匹配到有效签到的记录，按地点分组统计数量，即为未出现的次数

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