树形结构的数据怎么查出来

woshilaiwen_sql_de 2016-07-08 11:20:38

网上随便找的图。
tab1
id p_id code
1 0 a
2 1 b
3 1 c
4 2 d
5 2 e
6 2 f
7 3 g
8 3 h
9 4 i
10 5 j
11 9 k
12 7 l
13 7 m
14 8 n
15 13 o
16 14 p
差不多就是这样的数据
p_id就是上级节点
我有了o的id怎么查出来他的上级一直到a就是，acgmo五条数据
有了d怎么查出来dba三条数据
最顶层的A节点判断条件就是p_id=0

...全文

410 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

woshilaiwen_sql_de 2016-07-08

打赏
举报

引用 5 楼 ap0405140 的回复:


create table [ceshi]
(id int, parentid int, code nvarchar(50))

insert into [ceshi]
  select 1,0,'a' union all
  select 2,1,'b' union all
  select 3,1,'c' union all
  select 4,2,'d' union all
  select 5,2,'e' union all
  select 6,2,'f' union all
  select 7,3,'g' union all
  select 8,3,'h' union all
  select 9,4,'i' union all
  select 10,5,'j' union all
  select 11,9,'k' union all
  select 12,7,'l' union all
  select 13,7,'m' union all
  select 14,8,'n' union all
  select 15,13,'o' union all
  select 16,14,'p'


declare @x varchar(5)
select @x='o';

with t as
(select id,parentid,code,lv=0 from [ceshi] where code=@x
  union all
 select b.id,b.parentid,b.code,a.lv+1
   from t a
   inner join [ceshi] b on a.parentid=b.id)
select top 1 code
  from t 
  order by lv desc

/*
code
--------------------------------------------------
a

(1 row(s) affected)
*/

感谢..

唐诗三百首 2016-07-08

打赏
举报



create table [ceshi]

(id int, parentid int, code nvarchar(50))



insert into [ceshi]

  select 1,0,'a' union all

  select 2,1,'b' union all

  select 3,1,'c' union all

  select 4,2,'d' union all

  select 5,2,'e' union all

  select 6,2,'f' union all

  select 7,3,'g' union all

  select 8,3,'h' union all

  select 9,4,'i' union all

  select 10,5,'j' union all

  select 11,9,'k' union all

  select 12,7,'l' union all

  select 13,7,'m' union all

  select 14,8,'n' union all

  select 15,13,'o' union all

  select 16,14,'p'





declare @x varchar(5)

select @x='o';



with t as

(select id,parentid,code,lv=0 from [ceshi] where code=@x

  union all

 select b.id,b.parentid,b.code,a.lv+1

   from t a

   inner join [ceshi] b on a.parentid=b.id)

select top 1 code

  from t 

  order by lv desc



/*

code

--------------------------------------------------

a



(1 row(s) affected)

*/