SQL Server技术求助：如何生成未出现在指定表中的设施与日期组合列表？

嘿，你已经走到最后50米了，就差几个小细节调整！你的整体思路完全正确——先生成所有设施和日期的可能组合，再排除已经存在于table1中的组合，但你的NOT EXISTS子查询里有几个错误导致结果不对：

• 变量引用错误：你在子查询里写了between StartDate and EndDate，但这里应该用你声明的变量@StartDate和@EndDate，漏了@符号会导致SQL无法识别变量
• 无意义的空值判断：t2.FACILITY is null这个条件完全没必要，因为cte_FacilityReportingDates里的Facility是从table1取的去重值，不可能为空，加上这个条件会让子查询永远找不到匹配记录，最终NOT EXISTS会返回所有组合，这显然不是你要的结果
• 多余的GROUP BY：NOT EXISTS只需要判断是否存在匹配的记录，子查询里的group by t1.facility, t1.REPORTING_DATE完全多余，反而会干扰判断

修正后的完整代码 #

declare @StartDate date = '08/01/2021', @EndDate Date = '08/04/2021';

with cte_FacilityReportingDates as (
 -- 提取table1中已存在的设施-日期组合
 select distinct Facility, REPORTING_DATE 
 from table1 
 where REPORTING_DATE between @StartDate and @EndDate
),
cte_facility as (
 -- 获取所有唯一设施
 select distinct Facility from table1
),
cte_ReportingDates as (
 -- 获取指定日期范围内的所有唯一日期
 select distinct REPORTING_DATE 
 from table1 
 where REPORTING_DATE between @StartDate and @EndDate
),
cte_Combine as (
 -- 生成所有可能的设施-日期笛卡尔积
 select f.Facility, d.REPORTING_DATE 
 from cte_facility f cross join cte_ReportingDates d
)
-- 从所有组合中排除已存在的记录
select t1.FACILITY, t1.REPORTING_DATE 
from cte_Combine t1
where not exists (
 select 1 
 from cte_FacilityReportingDates t2 
 where t1.FACILITY = t2.FACILITY 
 and t1.REPORTING_DATE = t2.REPORTING_DATE -- 直接匹配日期，无需重复判断范围
);

额外优化建议 #

如果你的table1中日期可能不连续（比如某些日期没有任何记录），而你需要生成完整的连续日期范围（而非仅取table1中存在的日期），可以把cte_ReportingDates改成生成连续日期的CTE，示例如下：

cte_ReportingDates as (
 -- 生成@StartDate到@EndDate的连续日期
 select @StartDate as REPORTING_DATE
 union all
 select dateadd(day, 1, REPORTING_DATE)
 from cte_ReportingDates
 where REPORTING_DATE < @EndDate
)

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