是否可以联合两个具有相同结构的CTE的结果集
问题描述:
我想联合以下CTE的结果集,以便获得4行数据。是否可以联合两个具有相同结构的CTE的结果集
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
但是,我得到一个语法错误提示我不能使用UNION ALL两个热膨胀系数之间。 我该如何解决这个问题?
答
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
答
是的,但不是你正在做的方式。试试吧:
;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
你可以有多个CTE链接在一起。只有一个“WITH”和CTE之间的逗号。然后在CTE后面的声明中,您可以参考任何CTE。
答
;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
谢谢!我会接受这个,因为这是我得到的第一个回应!干杯! – 2010-09-10 15:06:43