create table tree(id int, pid int, name varchar(20)) insert into tree select 1, 0, 'xx' union all select 2, 1, 'xxx' union all select 3, 2, 'xxxx' union all select 4, 0, 'aa' union all select 5, 4, 'aaa' union all select 6, 5, 'aaaa' declare @id int set @id = 3 while(select pid from tree where id=@id)<>0 select @id=id from tree where id=(select pid from tree where id=@id) select * from tree where id=@id
create table tree(id char(1) ,pid char(1),name varchar(10)) insert tree select '1', '0' , 'xx' union select '2', '1', 'xxx' union select '3', '2' , 'xxxx' union select '4', '0' , 'aa' union select '5', '4' , 'aaa' union select '6', '5' , 'aaaa'-----查询函数 CREATE FUNCTION f_Pid(@ID char(3)) RETURNS @t_Level TABLE(ID char(3),Level int) AS BEGIN DECLARE @Level int SET @Level=1 INSERT @t_Level SELECT @ID,@Level WHILE @@ROWCOUNT>0 BEGIN SET @Level=@Level+1 INSERT @t_Level SELECT a.PID,@Level FROM tree a,@t_Level b WHERE a.ID=b.ID AND b.Level=@Level-1 END RETURN END GO-----查询语句 SELECT a.* FROM tree a,f_Pid('3') b WHERE a.ID=b.ID and a.pid=0
SELECT id1=sum( CASE id WHEN 4 THEN 0 WHEN 5 THEN 0 WHEN 6 THEN 0 WHEN 7 THEN 1 WHEN 8 THEN 2 WHEN 9 THEN 3 WHEN 10 THEN 1 WHEN 13 THEN 0 WHEN 14 THEN 0 END ), id2=sum( CASE id WHEN 11 THEN 0 WHEN 12 THEN 0
insert into tree select 1, 0, 'xx'
union all select 2, 1, 'xxx'
union all select 3, 2, 'xxxx'
union all select 4, 0, 'aa'
union all select 5, 4, 'aaa'
union all select 6, 5, 'aaaa'
declare @id int
set @id = 3
while(select pid from tree where id=@id)<>0
select @id=id from tree where id=(select pid from tree where id=@id)
select * from tree where id=@id
insert tree
select '1', '0' , 'xx'
union select '2', '1', 'xxx'
union select '3', '2' , 'xxxx'
union select '4', '0' , 'aa'
union select '5', '4' , 'aaa'
union select '6', '5' , 'aaaa'-----查询函数
CREATE FUNCTION f_Pid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
DECLARE @Level int
SET @Level=1
INSERT @t_Level SELECT @ID,@Level
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.PID,@Level
FROM tree a,@t_Level b
WHERE a.ID=b.ID
AND b.Level=@Level-1
END
RETURN
END
GO-----查询语句
SELECT a.*
FROM tree a,f_Pid('3') b
WHERE a.ID=b.ID and a.pid=0
id1=sum(
CASE id
WHEN 4 THEN 0
WHEN 5 THEN 0
WHEN 6 THEN 0
WHEN 7 THEN 1
WHEN 8 THEN 2
WHEN 9 THEN 3
WHEN 10 THEN 1
WHEN 13 THEN 0
WHEN 14 THEN 0
END ),
id2=sum(
CASE id
WHEN 11 THEN 0
WHEN 12 THEN 0
END )
FROM tb