如何移除多SELECT的UNION ALL并合并为单查询（月度批量需求）

兄弟，我懂你手动写30次重复SELECT的痛苦！直接给你两个可行的方案，一次性解决月度场景的需求，再也不用复制粘贴重复代码了：

方案一：CTE生成日期序列（兼容所有SQL Server版本） #

核心思路是先生成一个包含最近30天的日期列表，再和你的主表做关联计算，替代原来的UNION ALL拼接：

WITH DateRange AS (
    -- 生成最近30天的日期（从今天往前推29天到今天）
    SELECT CAST(DATEADD(day, -n, GETDATE()) AS DATE) AS TargetDate
    FROM (
        VALUES (0), (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)
    ) AS Numbers(n)
)
SELECT 
    dr.TargetDate, -- 新增日期列，方便区分每行对应哪天的数据（不需要可以删掉）
    CASE WHEN CAST(ILE.[Posting Date] AS DATE) < dr.TargetDate THEN CAST(ILE.Quantity AS NUMERIC(19,6)) ELSE 0 END AS [Opening],
    CASE WHEN CAST(ILE.[Posting Date] AS DATE) <= dr.TargetDate THEN CAST(ILE.Quantity AS NUMERIC(19,6)) ELSE 0 END AS [Closing]
FROM [Item Ledger Entry] ILE
CROSS JOIN DateRange dr
WHERE CAST(ILE.[Posting Date] AS DATE) <= dr.TargetDate
ORDER BY dr.TargetDate DESC;

说明： #

• 调整日期范围：只要修改Numbers里的数值就行，比如要生成最近60天，就补充到59的数值；
• 逻辑和你原来的完全一致：每个日期对应的Opening和Closing计算规则和之前的UNION ALL段完全匹配；
• 结果格式：和你原来的输出结构一致，多了TargetDate列方便区分日期（建议保留，不然你分不清每行对应哪天）。

方案二：用GENERATE_SERIES（SQL Server 2022+/Azure SQL专属） #

如果你的数据库是SQL Server 2022或Azure SQL，可以用GENERATE_SERIES函数更简洁地生成日期序列，不用手动写一堆VALUES：

WITH DateRange AS (
    -- 生成0到29的数字，对应最近30天的偏移量
    SELECT CAST(DATEADD(day, -value, GETDATE()) AS DATE) AS TargetDate
    FROM GENERATE_SERIES(0, 29)
)
SELECT 
    dr.TargetDate,
    CASE WHEN CAST(ILE.[Posting Date] AS DATE) < dr.TargetDate THEN CAST(ILE.Quantity AS NUMERIC(19,6)) ELSE 0 END AS [Opening],
    CASE WHEN CAST(ILE.[Posting Date] AS DATE) <= dr.TargetDate THEN CAST(ILE.Quantity AS NUMERIC(19,6)) ELSE 0 END AS [Closing]
FROM [Item Ledger Entry] ILE
CROSS JOIN DateRange dr
WHERE CAST(ILE.[Posting Date] AS DATE) <= dr.TargetDate
ORDER BY dr.TargetDate DESC;

性能优化小提示 #

如果你的[Item Ledger Entry]表数据量很大，建议优化一下查询避免索引失效：

• 把CAST(ILE.[Posting Date] AS DATE)的写法改成直接用日期比较，比如：
  • 把WHERE CAST(ILE.[Posting Date] AS DATE) <= dr.TargetDate改成WHERE ILE.[Posting Date] < DATEADD(day, 1, dr.TargetDate)
  • CASE语句里的CAST(ILE.[Posting Date] AS DATE) < dr.TargetDate改成ILE.[Posting Date] < dr.TargetDate
• 给[Posting Date]字段加索引，能大幅提升查询速度。

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