你可以两次cte,比如: ;with cte as (......),cte1 as (select * from cte where xxx) select * from cte1 where xxxx
实践证明:可以;WITH cte AS (SELECT 1 id UNION ALL SELECT 2), cte2 AS ( SELECT * FROM cte ) SELECT a.id AS AID,B.ID AS BID FROM cte a inner join cte2 b ON a.id=b.id/* AID BID ----------- ----------- 1 1 2 2 */
直观一点:;WITH cte AS (SELECT 1 id UNION ALL SELECT 2), cte2 AS ( SELECT * FROM cte ) SELECT *,'FirstCTE' FROM cte2 UNION ALL SELECT *,'SecondCTE' FROM cte /* id ----------- --------- 1 FirstCTE 2 FirstCTE 1 SecondCTE 2 SecondCTE */
CTE只对当前批有用 也就是说只能调用一次;with f as ( select * from tb )select * from f -- 如果再select * from f as a inner join b on ..就会报错
CTE只对当前批有用 也就是说只能调用一次if object_id('tempdb..#temp') is not null drop table #temp;with f as ( select * from tb )select * into #temp from f -- 如果再select * from #temp as a inner join b on ..就会报错
;with cte as
(......),cte1 as (select * from cte where xxx)
select *
from cte1 where xxxx
cte2 AS
(
SELECT * FROM cte
)
SELECT a.id AS AID,B.ID AS BID FROM cte a inner join cte2 b ON a.id=b.id/*
AID BID
----------- -----------
1 1
2 2
*/
cte2 AS
(
SELECT * FROM cte
)
SELECT *,'FirstCTE' FROM cte2
UNION ALL
SELECT *,'SecondCTE' FROM cte
/*
id
----------- ---------
1 FirstCTE
2 FirstCTE
1 SecondCTE
2 SecondCTE
*/
也就是说只能调用一次;with f as
(
select * from tb
)select * from f
--
如果再select * from f as a inner join b on ..就会报错
CTE只对当前批有用
也就是说只能调用一次if object_id('tempdb..#temp') is not null
drop table #temp;with f as
(
select * from tb
)select * into #temp from f
--
如果再select * from #temp as a inner join b on ..就会报错