如果不满足限制，则将默认行添加到查询结果

当我想到你想要完成的事情时，我会用“简单的英语”这样描述它：

• 返回一些查询的结果
• 但如果不存在结果，则返回一些默认值。

我的思考过程立即飞跃了“如果我总是包含默认值，但是当存在实际结果时以某种方式将它们过滤掉怎么办？

在考虑了我早上喝的咖啡后，我意识到这实际上很容易用 CTE 完成。不需要递归，但我将使用两个 CTE。

那个真正的询问

让我们首先将您的真实查询放入 CTE。这使得从查询中多次引用结果变得很容易，非常容易。在这个例子中，我只是要查询 sys.objects，并将整个该死的东西放入 CTE：

DECLARE @ObjectName nvarchar(128) = N'sysschobjs';

RealQuery AS (
    SELECT object_id, name
    FROM sys.objects
    WHERE name = @ObjectName
    )
SELECT *
FROM RealQuery;

现在为默认值

我在这里做同样的处理。只需制作一些默认占位符，并将它们抽象为我可以轻松引用的 CTE。也许您的默认值存储在某个表中的某个地方，或者您可能更喜欢将它们填充到#temptable 或 table 中@variable，在这种情况下，您不需要在这里使用 CTE。

WITH Defaults AS (
    SELECT *
    FROM (VALUES (1,N'One'),(2,N'Two'),(3,N'Three')) AS x(Id,Name)
    )
SELECT *
FROM Defaults;

混搭的时间

现在，在 CTE 中使用我们的真实查询，在另一个中使用我们的“默认”值，我只UNION ALL需要“真实”结果和默认占位符。“魔术”WHERE NOT EXISTS (SELECT 1 FROM RealQuery)用于控制是否包含这些默认值。

这将返回与对象名称匹配的单行sysschobjs，并且不返回默认占位符：

DECLARE @ObjectName nvarchar(128) = N'sysschobjs';

WITH Defaults AS (
    SELECT *
    FROM (VALUES (1,N'One'),(2,N'Two'),(3,N'Three')) AS x(Id,Name)
    ),
RealQuery AS (
    SELECT object_id, name
    FROM sys.objects
    WHERE name = @ObjectName
    )
SELECT *
FROM RealQuery
UNION ALL
SELECT *
FROM Defaults
WHERE NOT EXISTS (SELECT 1 FROM RealQuery);

如果您将第一行更改为不存在的值，sys.objects那么您将获得占位符默认结果：

DECLARE @ObjectName nvarchar(128) = N'AMtwo';

还有其他方法。

我的解决方案有效，但可能并不理想。例如，如果您查看执行计划，您会看到它正在运行两次“真实”查询。对于这个琐碎的查询，这完全没问题，但对于其他可能无法正常工作的情况。

您最好简单地运行“真实”查询以插入#Results临时表，然后检查临时表中有多少行。

IF EXISTS (SELECT 1 FROM #Results)
BEGIN
    INSERT INTO #Results(Id,Name)
    SELECT *
    FROM (VALUES (1,N'One'),(2,N'Two'),(3,N'Three')) AS x(Id,Name);
END;

SELECT *
FROM #Results;

可以使用 pivot 和 unpivot 来有效地实现这一点。

使用稍微扩展的测试数据：

要生成第 7到^第11^行：

WITH TheQuery AS
(
    SELECT V.*
    FROM 
    (
        VALUES
            (1, CONVERT(datetime2(0), '20220208', 112), 'Connected', 'False'),
            (1, CONVERT(datetime2(0), '20220210', 112), 'Connected', 'False'),
            (2, CONVERT(datetime2(0), '20220209', 112), 'Connected', 'False')
    ) AS V (DeviceId, [Time], Context, [Value])
)
SELECT
    U.DeviceId, 
    U.TheDate, 
    Context = IIF(U.Present = 1, U.Context, 'Fake'), 
    [Value] = IIF(U.Present = 1, U.[Value], 'False')
FROM TheQuery AS Q
PIVOT (COUNT([Time]) FOR [Time] IN 
    ([20220207],[20220208],[20220209],[20220210],[20220211])) AS P
UNPIVOT (Present FOR TheDate IN 
    ([20220207],[20220208],[20220209],[20220210],[20220211])) AS U;

枢轴部分将数据转换为：

如果COUNT值不存在，则聚合结果为零，否则为 1。unpivot 将集合旋转回行，其中计数产生零的额外行：

计数中的“当前”列提供了一种简单的方法来决定是否应使用默认值。

最终结果是：

执行计划：

该方法适用于动态 SQL，因为数据透视表和非数据透视表都需要一串带引号的逗号分隔日期。

可以通过多种方式创建此字符串，例如：

DECLARE 
    @Start datetime2(0) = '20220207',
    @End datetime2(0) = '20220211';

WITH R AS 
(
    SELECT TheDate = @Start
    UNION ALL
    SELECT DATEADD(DAY, 1, R.TheDate)
    FROM R
    WHERE R.TheDate < @End
)
SELECT 
    STRING_AGG(QUOTENAME(R.TheDate), ',')
FROM R;

这个答案很酷，但为了填补缺失的日期范围，我的偏好只是通过使用DateDimensions表格将其踢出经典。这是 Aaron Bertrand 如何生成一个示例。

当然，如果您不需要所有额外的维度，您可以只为日期生成它。DateDimensions然后，您只需要通过另一个表中的日期字段对您的表进行简单的外部连接，以填补您缺少的日期的空白，如下所示：

SELECT 
    ISNULL(YT.DeviceId, 1) AS DeviceId, 
    DD.[Date] AS [Time], 
    ISNULL(YT.Context, 'Fake') AS Context,
    ISNULL(YT.[Value], 'False') AS [Value]
FROM DateDimensions AS DD
LEFT JOIN YourTable AS YT
    ON DD.[Date] = YT.[Time]
WHERE DD.[Date] >= '2022-02-07'
    AND DD.[Date] <= '2022-02-10'

对于非常大的日期范围和数据集，这可能不是性能排名第一的方法，但我发现它最简单，通常足以让你越过终点线，尤其是在正确索引的情况下。