代码之家 › 专栏 › 技术社区 › Julius A

两个具有相同结构的CTE的结果集是否可以合并

sql-server-2005

2

Julius A · 技术社区 · 15 年前

Id  Name
-------------
1    Test1  
2   Test2  
3   Test3  
4   Test4

我想使用的Sql如下

;with CTE1 (Id,Name)
as
( 
    select 1 as Id, 'Test1' as Name
    union all
    select 2, 'Test2'
)
select * from CTE1
union all
;with CTE2 (Id,Name)
as
( 
    select 3 as Id, 'Test3' as Name
    union all
    select 4, 'Test4'
)
select * from CTE2

但是,我得到了一个语法错误,提示我不能在两个CTE之间使用Union All。

3 回复 | 直到 15 年前

1

0

Joe Phillips 15 年前

with CTE1 (Id,Name)
as
( 
    select 1 as Id, 'Test1' as Name
    union all
    select 2, 'Test2'
),
CTE2 (Id,Name)
as
( 
    select * from CTE1
    union all
    select 3 as Id, 'Test3' as Name
    union all
    select 4, 'Test4'
)
select * from CTE2

或

with CTE1 (Id,Name)
as
( 
    select 1 as Id, 'Test1' as Name
    union all
    select 2, 'Test2'
),
CTE2 (Id,Name)
as
( 
    select 3 as Id, 'Test3' as Name
    union all
    select 4, 'Test4'
)
select * from CTE1
union all
select * from CTE2

2

1

KM. 15 年前

是的,但不是你现在的方式。这样试试:

;with CTE1 (Id,Name)
as
( 
    select 1 as Id, 'Test1' as Name
    union all
    select 2, 'Test2'
)
,CTE2 (Id,Name)
as
( 
    select 3 as Id, 'Test3' as Name
    union all
    select 4, 'Test4'
)
select * from CTE1
union all
select * from CTE2

3

1

Joe Phillips 15 年前

;with CTE1 (Id,Name) 
as 
(  
    select 1 as Id, 'Test1' as Name 
    union all 
    select 2, 'Test2' 
) 
,CTE2 (Id,Name) 
as 
(  
    select 3 as Id, 'Test3' as Name 
    union all 
    select 4, 'Test4' 
) 
select * from CTE1
union all
select * from CTE2