父子表排序问题

[muzhenxing013]

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

这里pid是id的上级，就是他的父级，我现在想要的时候，查询出来的结果是把父级别和对应的子级排到一起，而且父级在上边，其实就是，1243的循序了

[百年树人]

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 行）
**/

[feixianxxx]

给你个参考

表 
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', NULL

DECLARE @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=0

WHILE @@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
END

SELECT * FROM @T

[liangCK]

改一下.不用null,用0表示根节点

CREATE TABLE b(ID INT ,NAME CHAR(1),PId INT )
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' , 0

SELECT 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 行受影响)

*/

[feixianxxx]

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 
(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
*/
 

[feixianxxx]

[Quote=引用 1 楼 josy 的回复:]
SQL codeCREATETABLE b(IDINT ,NAMECHAR(1),PIdINT )insertinto bselect1 ,'A' ,NULLinsertinto bselect2 ,'B' ,1insertinto bselect3 ,'D' ,4insertinto bselect4 ,'C' ,NULLselect*from borderbyisnull(pid,id¡­
[/Quote]
树哥的多加一条记录就不对了

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

[JonasFeng]

[Quote=引用 1 楼 josy 的回复:]
SQL codeCREATETABLE b(IDINT ,NAMECHAR(1),PIdINT )insertinto bselect1 ,'A' ,NULLinsertinto bselect2 ,'B' ,1insertinto bselect3 ,'D' ,4insertinto bselect4 ,'C' ,NULLselect*from borderbyisnull(pid,id¡­
[/Quote]

正解。

[-狙击手-]

...

[feixianxxx]

大家都理解错楼主意思了。。
要父子排序的

[ChinaJiaBing]

来晚了...
3楼正解.

[feixianxxx]

[Quote=引用 9 楼 chinajiabing 的回复:]
来晚了...
3楼正解.
[/Quote]
三楼的也不对。。
加几条数据就看出来了

[liangCK]

我的查询最多可以处理3级..

[muzhenxing013]

如果把父级当0，来排序的话，这样就会把父级排到一起的，

[liangCK]

[Quote=引用 12 楼 muzhenxing013 的回复:]
如果把父级当0，来排序的话，这样就会把父级排到一起的，
[/Quote]

看3楼.最多可以排3层.

[muzhenxing013]

好的

[feixianxxx]

[Quote=引用 14 楼 muzhenxing013 的回复:]
好的
[/Quote]
LZ可以了么？