能否同时使用多个WITH语句与UNION子句？SQL技术问询

1. 能否同时使用多个WITH和UNION？ #

可以，但你当前的写法不符合SQL语法规范。正确的做法有两种：一是将多个CTE（WITH定义的临时表）放在同一个WITH块中用逗号分隔；二是在UNION的每个分支里单独定义CTE（部分数据库如SQL Server支持）。更高效的方式是合并重复逻辑，避免冗余代码。

2. 解决MemberMonth重复统计的问题 #

你遇到的重复统计问题，根源是拆分查询时重复计算，或合并查询时未按servicetype（IP/Phys）做精准分组。以下是优化后的方案：

合并逻辑的高效SQL #

WITH CombinedData AS (
    SELECT DISTINCT
        period,
        type,
        servicetype, -- 保留服务类型，明确区分IP/Phys
        MAX(period) OVER (PARTITION BY type, servicetype) AS MaxPeriod, -- 按类型+服务类型分组取最大周期
        MemberMonth AS MemCnt
    FROM
        database
    WHERE
        lob = 'commercial'
        AND segmentproduct IN ('Indiv ACA', 'Indiv Legacy', 'Large Group FI-NR', 'Small Grp ACA', 'Small Grp Legacy')
        AND servicetype IN ('IP', 'Phys') -- 同时筛选两种服务类型
        AND paidthrough = (SELECT MAX(paidthrough) FROM database WITH (NOLOCK)) -- 用=替代IN提升效率
    GROUP BY
        period, type, servicetype, MemberMonth -- 加入servicetype分组，避免跨类型统计重复
)
SELECT
    period,
    CONCAT(type, ' - ', servicetype) AS type, -- 按需组合类型与服务类型，或单独保留servicetype列
    MemCnt,
    CASE WHEN period = MaxPeriod THEN 'Current Period' ELSE 'Prior Period' END AS [Prior Current]
FROM CombinedData

多CTE加UNION的正确写法（SQL Server为例） #

如果坚持拆分CTE，可采用如下语法：

WITH PData AS (
    SELECT DISTINCT
        period, type, MAX(period) OVER (PARTITION BY type) AS MaxPeriod, MemberMonth AS MemCnt
    FROM database
    WHERE
        lob = 'commercial'
        AND segmentproduct IN ('Indiv ACA', 'Indiv Legacy', 'Large Group FI-NR', 'Small Grp ACA', 'Small Grp Legacy')
        AND servicetype = 'IP'
        AND paidthrough = (SELECT MAX(paidthrough) FROM database WITH (NOLOCK))
    GROUP BY period, type, MemberMonth
),
PData2 AS (
    SELECT DISTINCT
        period, type, MAX(period) OVER (PARTITION BY type) AS MaxPeriod, MemberMonth AS MemCnt
    FROM database
    WHERE
        lob = 'commercial'
        AND segmentproduct IN ('Indiv ACA', 'Indiv Legacy', 'Large Group FI-NR', 'Small Grp ACA', 'Small Grp Legacy')
        AND servicetype = 'Phys'
        AND paidthrough = (SELECT MAX(paidthrough) FROM database WITH (NOLOCK))
    GROUP BY period, type, MemberMonth
)
SELECT period, type, MemCnt,
       CASE WHEN period = MaxPeriod THEN 'Current Period' ELSE 'Prior Period' END AS [Prior Current]
FROM PData
UNION ALL -- 确认无重复时用UNION ALL，比UNION更高效（避免自动去重开销）
SELECT period, type, MemCnt,
       CASE WHEN period = MaxPeriod THEN 'Current Period' ELSE 'Prior Period' END AS [Prior Current]
FROM PData2

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