You need to enable JavaScript to run this app. WITH子句嵌套外层SELECT时SQL Server语法错误,Oracle可正常运行
优惠活动
大模型
产品
解决方案
定价
更多

WITH子句嵌套外层SELECT时SQL Server语法错误,Oracle可正常运行

问题原因

SQL Server 对WITH公共表表达式的语法限制比Oracle严格,要求WITH子句必须位于单条查询语句的最开头,不支持嵌套在子查询内部使用,这就是你把WITH包在子查询里抛出语法错误的核心原因。

改写后的合法查询(兼容SQL Server)

你不需要创建视图,只要把WITH提到整个查询的最外层即可,改写后代码如下:

WITH  got_debit_custno (custno, slipno, category, donation, debit_custno)  AS
(
    SELECT  custno, slipno, category, donation
    ,   CASE
          WHEN category = 'CREDIT'
          THEN 'N/A'
          ELSE custno
        END
    FROM    a
)
,  prep (custno, slipno, category, donation, debit_custno, rn, rdt) AS
(
    SELECT  dc.*
    ,   ROW_NUMBER ()  OVER (PARTITION BY debit_custno, category ORDER BY donation, slipno)
    ,       SUM (donation) OVER (PARTITION BY debit_custno, category ORDER BY donation, slipno)
    FROM    got_debit_custno dc
)
,  r (custno, slipno, category, donation, debit_custno, rn, rdt, mn) AS
(
    SELECT  custno, slipno, category, donation, debit_custno
    ,       CASE
            WHEN rn = MAX(rn) OVER (PARTITION BY debit_custno, category)
            THEN rn
        END
    ,       rdt, 1
    FROM    prep
    WHERE   rdt <= 27000 OR rn = 1
 UNION ALL
    SELECT  p.custno, p.slipno, p.category, p.donation, p.debit_custno
    ,       CASE
            WHEN p.rn = MAX(p.rn) OVER (PARTITION BY p.debit_custno, p.category)
            THEN p.rn
        END
    ,       p.rdt, r.mn + 1
    FROM    prep p
    JOIN    r    ON  p.debit_custno = r.debit_custno
             AND p.category = r.categoRy
             AND p.rn > r.rn
                         AND (p.rdt <= r.rdt + 27000 OR p.rn = r.rn + 1)
)
, final_result AS (
    SELECT    custno, slipno, category, donation
    ,         DENSE_RANK () OVER (ORDER BY debit_custno, category, mn) AS unique_nm
    FROM      r
)
SELECT custno, slipno, category, donation, unique_nm 
FROM final_result
ORDER BY  custno, unique_nm, donation

直接在MERGE中使用的方法

你可以直接把上面的CTE结构放在MERGE语句的开头,作为MERGE的源表使用,示例结构如下:

WITH  got_debit_custno (custno, slipno, category, donation, debit_custno)  AS
(
    -- 同上逻辑
),
prep AS (
    -- 同上逻辑
),
r AS (
    -- 同上逻辑
),
merge_source AS (
    SELECT    custno, slipno, category, donation
    ,         DENSE_RANK () OVER (ORDER BY debit_custno, category, mn) AS unique_nm
    FROM      r
)
MERGE INTO 你的目标表 t
USING merge_source s
ON t.匹配字段 = s.匹配字段
WHEN MATCHED THEN
    UPDATE SET 
        t.custno = s.custno,
        t.slipno = s.slipno
        -- 其他需要更新的字段
WHEN NOT MATCHED THEN
    INSERT (custno, slipno, category, donation, unique_nm)
    VALUES (s.custno, s.slipno, s.category, s.donation, s.unique_nm);

内容的提问来源于stack exchange,提问作者user15940336

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.17 04:15:06