CREATE TABLE b(ID INT ,NAME CHAR(1),PId INT )
insert into b select 1 ,'A' , NULL
insert into b select 2 ,'B' , 1
insert into b select 3 ,'D' , 4
insert into b select 4 ,'C' , NULL
select * from b order by isnull(pid,id),case when pid is null then 1 else 2 end/**
ID NAME PId
----------- ---- -----------
1 A NULL
2 B 1
4 C NULL
3 D 4(所影响的行数为 4 行)
**/
insert into b select 1 ,'A' , NULL
insert into b select 2 ,'B' , 1
insert into b select 3 ,'D' , 4
insert into b select 4 ,'C' , NULL
select * from b order by isnull(pid,id),case when pid is null then 1 else 2 end/**
ID NAME PId
----------- ---- -----------
1 A NULL
2 B 1
4 C NULL
3 D 4(所影响的行数为 4 行)
**/
表
ID PID NAME VALUE
1 0 A
2 1 B
3 0 C
4 2 D
5 3 E
6 4 F 更新后结果
ID PID NAME VALUE
1 0 A A
2 1 B AB
3 0 C C
4 2 D ABD
5 3 E CE
6 4 F ABDF 这里的value=name+(pid对应的ID的value)!!
方法1:
create table tb(ID int,PID int,NM varchar(5),VL varchar(10))
insert into tb select 1,0,'A',null
insert into tb select 2,1,'B',null
insert into tb select 3,0,'C',null
insert into tb select 4,2,'D',null
insert into tb select 5,3,'E',null
insert into tb select 6,4,'F',null
go
select id,pid,nm,vl=convert(varchar(10),nm) into #t from tb where pid=0
while exists(select 1 from tb a where not exists(select 1 from #t where id=a.id))
insert into #t
select a.id,a.pid,a.nm,vl=b.vl+a.nm from tb a inner join #t b on a.pid=b.id
where not exists(select 1 from #t where id=a.id)
select * from #t order by id
go
drop table tb,#t
/*----------------------------
1 0 A A
2 1 B AB
3 0 C C
4 2 D ABD
5 3 E CE
6 4 F ABDF
---------------------------------*/方法2:
DECLARE @TB TABLE([ID] INT, [PID] INT, [NAME] VARCHAR(1), [VALUE] VARCHAR(10))
INSERT @TB
SELECT 1, 0, 'A', NULL UNION ALL
SELECT 2, 1, 'B', NULL UNION ALL
SELECT 3, 0, 'C', NULL UNION ALL
SELECT 4, 2, 'D', NULL UNION ALL
SELECT 5, 3, 'E', NULL UNION ALL
SELECT 6, 4, 'F', NULLDECLARE @LVL INT
DECLARE @T TABLE([ID] INT, [PID] INT, [NAME] VARCHAR(1), [VALUE] VARCHAR(10),LVL INT)SET @LVL=1
INSERT @T
SELECT ID,PID,NAME,NAME,@LVL FROM @TB WHERE PID=0WHILE @@ROWCOUNT>0
BEGIN
SET @LVL=@LVL+1
INSERT @T
SELECT A.ID,A.PID,A.NAME,T.VALUE+A.NAME,@LVL
FROM @TB AS A,@T AS T
WHERE A.PID=T.ID AND LVL=@LVL-1
ENDSELECT * FROM @T
insert into b select 1 ,'A' , 0
insert into b select 2 ,'B' , 1
insert into b select 3 ,'D' , 4
insert into b select 4 ,'C' , 0SELECT A.*
FROM b AS A
LEFT JOIN b AS B
ON A.PId = B.ID
ORDER BY CASE WHEN B.PId IS NULL THEN A.ID
WHEN B.Pid = 0 THEN B.ID
ELSE B.PId END,
CASE WHEN B.PId = 0 THEN A.ID
ELSE A.PId END,
A.ID;DROP TABLE b/*
ID NAME PId
----------- ---- -----------
1 A 0
2 B 1
4 C 0
3 D 4(4 行受影响)*/
insert into b select 1 ,'A' , NULL
insert into b select 2 ,'B' , 1
insert into b select 3 ,'D' , 4
insert into b select 4 ,'C' , NULL
select * from
(select bb.ID,bb.NAME,bb.PId from b bb,b bbb where bb.ID=bbb.PId
union all
select bbb.ID,bbb.NAME,bbb.PId from b bb,b bbb where bb.ID=bbb.PId) t
ORDER BY CASE WHEN PId IS NULL THEN ID
ELSE PId END,
CASE WHEN PId = 0 THEN ID
ELSE PId END,
ID;
/*
1 A NULL
2 B 1
4 C NULL
3 D 4
*/
CREATE TABLE b(ID INT ,NAME CHAR(1),PId INT )
insert into b select 1 ,'A' , NULL
insert into b select 2 ,'B' , 1
insert into b select 3 ,'D' , 4
insert into b select 4 ,'C' , NULL
insert into b select 5 ,'C' , 3
select * from b order by isnull(pid,id),case when pid is null then 1 else 2 end