时间戳

首页 > 解决方案 > SQL Server：如何制作一个包含不在另一个表中的日期和设施的列表？

sql-server - SQL Server：如何制作一个包含不在另一个表中的日期和设施的列表？

问题描述

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

with cte_FacilityReportingDates as 
(
    select distinct Facility, REPORTING_DATE 
    from table1 a  
    where REPORTING_DATE between @StartDate and @EndDate
),
cte_facility as
(
    select distinct Facility 
    from table1 a 
),
cte_ReportingDates as 
(
    select distinct a.REPORTING_DATE 
    from table1 a 
    where a.REPORTING_DATE between @StartDate and @EndDate
),
cte_Combine as
(
    select * 
    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 t2.REPORTING_DATE between StartDate and EndDate and t2.FACILITY is null group by t1.facility, t1.REPORTING_DATE)

我已经把它降到了比赛的最后 50 场（奥运会的帽子提示），但无法越过终点线。我知道这只是我忽略的事情，但我正在绞尽脑汁！我需要显示不在 cte_ReportingDates 结果中的设施和日期。

标签： sql-server

解决方案

通过适当的格式，您将鼓励其他人提供帮助。您在编辑代码时消除了其他人在格式化您的代码时所做的努力。老实说，这很令人沮丧。

正确格式化后，您可以清楚地看到每个 CTE 的定义位置，并更好地了解每个 CTE 的作用。似乎您过度使用了 DISTINCT - 不要只是将其放入代码中以希望它“修复”某些东西。如果用于测试是否存在，第一个 cte (cte_FacilityReportingDates) 并不真正需要 DISTINCT。TBH 那个特定的 CTE 它有点矫枉过正，因为逻辑可以很容易地合并到下面的 EXISTS 子句中 - 但这是一种风格选择。

<with  ... all your CTEs from original query ...> 
select comb.FACILITY, comb.REPORTING_DATE 
from cte_Combine comb 
where not exists (select * from cte_FacilityReportingDates as trn 
    where comb.FACILITY = trn.FACILITY 
      and comb.REPORTING_DATE = trn.REPORTING_DATE)
order by ...;

没有理由将 GROUP BY 子句应用于最终查询，因为对于一组唯一的 <FACILITY, REPORT_DATE> 来说它什么都不是。每当您使用/看到这样一个没有聚合的子句时，都应该担心作者已经失去了路径。

还要注意 ORDER BY 子句。如果行的顺序很重要，那么生成结果集的查询必须有一个。通常它确实很重要。

我还使用了更好的表别名。神秘的对读者没有帮助；养成良好的习惯。我不知道名为 cte_FacilityReportingDates 的 CTE 是什么（从“table1”中选择 - 另一个具有同样垃圾别名“a”的垃圾名称）所以我只是编造了一些东西。

我要强调的最后一个问题是您所做的相当重要的假设。您的逻辑假设每个设施都存在于 table1 中。对于某种“活动”表（这是我对该表代表什么的猜测），这通常不是一个安全的假设。这同样适用于日期。对于日期，您可以轻松地生成两个边界之间的所有日期集 - 如果需要，我会将调整留给您。您不能使用 for facility - 您可能（可能会或应该）需要另一张桌子。

推荐阅读