使用WITH子句后能否运行多条SELECT主查询语句？

不能直接在同一个WITH子句后接多条SELECT语句，这是因为CTE（公用表表达式）的作用域仅限于紧跟在它之后的第一条SQL语句，第二条SELECT执行时，之前定义的CTE已经失效，所以会报错。

解决方法： #

• 合并查询（兼容结构处理）：如果两个查询的结果可以通过调整结构合并，用UNION ALL将结果放在同一条查询里，还可以加标识列区分数据来源：

with 
a as (select a,b,c from Table1 with(readuncommitted)),
b as (select d,e,f from Table2 with(readuncommitted))
select 'Table1' as 数据来源, a, b, c, null as d, null as e, null as f from a
union all
select 'Table2' as 数据来源, null as a, null as b, null as c, d, e, f from b

• 重复定义CTE：每条SELECT语句前单独定义需要的CTE，确保每个查询都能正常调用对应的临时结果集：

with a as (select a,b,c from Table1 with(readuncommitted))
select * from a;

with b as (select d,e,f from Table2 with(readuncommitted))
select * from b;

• 存入临时表：如果数据库支持临时表（如SQL Server、MySQL等），可以先把CTE的结果存入临时表，之后就能多次查询临时表的数据：

with a as (select a,b,c from Table1 with(readuncommitted))
select * into #TempTable1 from a;

with b as (select d,e,f from Table2 with(readuncommitted))
select * into #TempTable2 from b;

select * from #TempTable1;
select * from #TempTable2;

-- 使用完毕后删除临时表
drop table #TempTable1;
drop table #TempTable2;

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