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
相关产品推荐
相关产品推荐

