主页 / dba / 问题 / 194179
Accepted
Zach Smith
Asked: 2017-12-30 06:59:47 +0800 CST

列是否有 UNION ALL 等效项？

  • 772

可以使用自引用 CTE 和 union all 在 SQL Server 中模拟循环，如下所示：

declare @a int = 1;
declare @b int = 5;

;with Tbl as (
    select @a a
    UNION ALL
    select (a + 1) a
    from Tbl
    where (a + 1) < @b
)
select * from Tbl

当您想要创建从开始日期到结束日期的日期列表然后在这些特定日期加入时，这很有用。

我也可以想象也想对列这样做。有没有一种方法可以在不使用动态 SQL 的情况下在 SQL Server 中实现这一点？我可能希望将这样的查询保存为视图，而不是 SP（尽管我知道这可以通过动态 SQL 来实现）

sql-server sql-server-2016

2 个回答

  1. Best Answer

    列是否有 UNION ALL 等效项？

    您正在谈论在 CTE 中使用 UNION 来生成日期。在这里，我展示了如何在列中生成日期。

    您可以使用PIVOT 子句。但是，有一个限制：必须事先知道列名。例如，如果你想显示每月的数据，你可以使用天数作为列名：

    SELECT *
FROM
    (SELECT DAY([Date]) AS d,
            MONTH([Date]) AS m,
            YEAR([Date]) AS y,
            Amount
     FROM MyTable) src
PIVOT (
    SUM(Amount)
    FOR d IN ( [1],  [2],  [3],  [4],  [5],  [6],  [7],  [8],  [9], [10],
              [11], [12], [13], [14], [15], [16], [17], [18], [19], [20],
              [21], [22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) piv;

    结果看起来像这样（我在 11 天时截取了图像，但实际上，最多有 31 列）：

    查询结果集

    如果必须显示更多日期，则可以使用自开始日期以来的天数作为列名。如果您基于此查询创建报告（使用某些报告工具），您可以将此数字添加到开始日期以再次获取列标题的真实日期。

    由于您必须明确指定列名，因此这部分不需要 CTE，但您可以将数据透视查询与 CTE 结合起来生成行。

    • 2

  2. 社区维基回答：

    可以使用自引用 CTE 和 union all 在 SQL Server 中模拟循环...

    这将很快达到 CTE 的递归限制，默认情况下通常为 100。尽管可以使用MAXRECURSION查询提示来扩展限制，但会影响性能。

    MAXRECURSION number
    指定此查询允许的最大递归数。number是介于 0 和 32767 之间的非负整数。指定 0 时，不应用限制。如果未指定此选项，则服务器的默认限制为 100。

    研究Tally Table和Numbers Table以获得更好、更有效的方法来做到这一点。这是一个关于计数/数字表的链接，这是一个关于创建日历表的链接。

    • 2

相关问题