with test2 as ( select * from test where a='3' union all select t1.* from test as t1 inner join test2 as t2 on t1.b=t2.a ) select * from test2 得出 ----这个能得出来吗? a b ----- 3 2 4 1 5 1
CREATE table test(a nvarchar(10),b int) DROP table test insert test select '1' ,0 union all select '2' ,1 union all select '3' ,2 union all select '4' ,1 union all select '5' ,1with test2 --没问题啊 as ( select * from test where a='3' union all select t1.* from test as t1 inner join test2 as t2 on t1.b=t2.a ) select * from test2 /* a b ----------- ----------- 3 2(1 行受影响)*/
还不如直接 select * from test 呢貌似写错了?
楼主的test2能求出楼主的结果?不能吧。真要这样的话,1、另个union 2、直接select * from test
with test1 as ( select *,lenth=1 from test where a='3' union all select t1.*,lenth =t2.lenth+1 from test as t1 inner join test1 as t2 on t1.a=t2.b ), test2 as ( select * from test where a=(select a from test1 where lenth =(select max(lenth) from test1)) union all select t1.* from test as t1 inner join test2 as t2 on t1.b=t2.a ) select * from test2
declare @id varchar(20) set @id='001012'; with cte_parent as ( select id,parentid from t_menu where id=@id union all select t_menu.id,t_menu.parentid from t_menu inner join cte_parent on t_menu.id=cte_parent.parentid ), cte_child as ( select id,parentid from t_menu where id=@id union all select t_menu.id,t_menu.parentid from t_menu inner join cte_child on t_menu.parentid=cte_child.id ) select * from cte_parent union select * from cte_child功能实现了,感谢8楼的建议
create table #test(a int,b int) insert #test values(1, 0) insert #test values(2, 1) insert #test values(3, 2) insert #test values(4, 1) insert #test values(5, 1) --select * from #test----找出根节点 declare @Root int ;with test1 as ( select *,Code = cast('00' as varchar(10)) from #test where a = 3 union all select t1.* ,cast(t2.Code +rtrim(t1.a) as varchar(10)) from #test t1 join test1 t2 on t1.a = t2.b ) select top(1) @Root = a from test1 order by len(code) desc ------找出树 ;with test1 as ( select *,Code = cast('00' as varchar(10)) from #test where a = @root union all select t1.* ,cast(t2.Code +rtrim(t1.a) as varchar(10)) from #test t1 join test1 t2 on t1.b = t2.a ) select a,b from test1 order by code /* a b ----------- ----------- 1 0 2 1 3 2 4 1 5 1(5 row(s) affected)*/
DROP table test
insert test
select '1' ,0 union all
select '2' ,1 union all
select '3' ,2 union all
select '4' ,1 union all
select '5' ,1with test2 --没问题啊
as
(
select * from test where a='3'
union all
select t1.* from test as t1 inner join test2 as t2 on t1.b=t2.a
)
select * from test2
/*
a b
----------- -----------
3 2(1 行受影响)*/
select * from test 呢貌似写错了?
2、直接select * from test
with test1
as
(
select *,lenth=1 from test where a='3'
union all
select t1.*,lenth =t2.lenth+1 from test as t1 inner join test1 as t2 on t1.a=t2.b
),
test2 as
(
select * from test where a=(select a from test1 where lenth =(select max(lenth) from test1))
union all
select t1.* from test as t1 inner join test2 as t2 on t1.b=t2.a
)
select * from test2
set @id='001012';
with cte_parent
as
(
select id,parentid from t_menu where id=@id
union all
select t_menu.id,t_menu.parentid from t_menu inner join cte_parent on t_menu.id=cte_parent.parentid
),
cte_child
as
(
select id,parentid from t_menu where id=@id
union all
select t_menu.id,t_menu.parentid from t_menu inner join cte_child on t_menu.parentid=cte_child.id
)
select * from cte_parent
union
select * from cte_child功能实现了,感谢8楼的建议
create table #test(a int,b int)
insert #test values(1, 0)
insert #test values(2, 1)
insert #test values(3, 2)
insert #test values(4, 1)
insert #test values(5, 1)
--select * from #test----找出根节点
declare @Root int
;with test1
as
(
select *,Code = cast('00' as varchar(10)) from #test where a = 3
union all
select t1.* ,cast(t2.Code +rtrim(t1.a) as varchar(10)) from #test t1 join test1 t2 on t1.a = t2.b
)
select top(1) @Root = a from test1 order by len(code) desc
------找出树
;with test1
as
(
select *,Code = cast('00' as varchar(10)) from #test where a = @root
union all
select t1.* ,cast(t2.Code +rtrim(t1.a) as varchar(10)) from #test t1 join test1 t2 on t1.b = t2.a
)
select a,b from test1 order by code
/*
a b
----------- -----------
1 0
2 1
3 2
4 1
5 1(5 row(s) affected)*/