找层次关系

jiajiaren 2012-01-08 08:03:00

现数据库有表的结构如下： Mark 表示几层，一个*一层，两个*两层，以此类推，PId是Id的父Id，Porder 是同一个父节点的排列顺序，Id可能不连续。
----------------------------------------------------------------------------------------------------
Id PId Mark Porder Name

11 5 *.*.*.*.*.* 1 Name11
12 1 *.* 3 Name12
13 2 *.*.* 1 Name13
18 1 *.* 2 Name18
15 18 *.*.* 1 Name15
3 1 *.* 1 Name3
1 0 * 1 Name1
2 12 *.*.* 1 Name2
4 13 *.*.* 1 Name4
5 15 *.*.*.*.* 1 Name5
6 15 *.*.*.*.* 2 Name6
7 15 *.*.*.*.* 3 Name7
8 15 *.*.*.*.* 4 Name8
20 15 *.*.*.*.* 5 Name20
10 5 *.*.*.*.*.* 2 Name10

现在需要得到下面的结果，就是要显示层次关系，例如：Id为3、18、12的父Id都为1，但Id为18的记录底下有子节点，所以先排Id为18的子节点，再排Id为12的记录，请问Sql怎么写？

Id PId Mark Porder Name

1 0 * 1 Name1
3 1 *.* 1 Name3
18 1 *.* 2 Name18
15 18 *.*.* 1 Name15
5 15 *.*.*.*.* 1 Name5
11 5 *.*.*.*.*.* 1 Name11
10 5 *.*.*.*.*.* 2 Name10
6 15 *.*.*.*.* 2 Name6
7 15 *.*.*.*.* 3 Name7
8 15 *.*.*.*.* 4 Name8
20 15 *.*.*.*.* 5 Name20
12 1 *.* 3 Name12
2 12 *.*.* 1 Name2
13 2 *.*.* 1 Name13
4 13 *.*.* 1 Name4

...全文

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

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

叶子 2012-01-08

打赏
举报



declare @T table (Id int,PId int,Mark varchar(11),Porder int,Name varchar(6))

insert into @T

select 11,5,'*.*.*.*.*.*',1,'Name11' union all

select 12,1,'*.*',3,'Name12' union all

select 13,2,'*.*.*',1,'Name13' union all

select 18,1,'*.*',2,'Name18' union all

select 15,18,'*.*.*',1,'Name15' union all

select 3,1,'*.*',1,'Name3' union all

select 1,0,'*',1,'Name1' union all

select 2,12,'*.*.*',1,'Name2' union all

select 4,13,'*.*.*',1,'Name4' union all

select 5,15,'*.*.*.*.*',1,'Name5' union all

select 6,15,'*.*.*.*.*',2,'Name6' union all

select 7,15,'*.*.*.*.*',3,'Name7' union all

select 8,15,'*.*.*.*.*',4,'Name8' union all

select 20,15,'*.*.*.*.*',5,'Name20' union all

select 10,5,'*.*.*.*.*.*',2,'Name10'



DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))

DECLARE @Level int SET @Level=0

INSERT @t_Level SELECT ID,@Level,ID FROM @T WHERE PID =0

WHILE @@ROWCOUNT>0

BEGIN

    SET @Level=@Level+1

    INSERT @t_Level SELECT a.ID,@Level,b.Sort+ltrim(a.porder)+ltrim(a.ID)

    FROM @T a,@t_Level b WHERE a.PID=b.ID AND b.Level=@Level-1

END



SELECT ID=ltrim(a.Id),PId,Mark,Porder,[Name]

FROM @T a,@t_Level b WHERE a.ID=b.ID ORDER BY b.Sort



--如果要你的那种现实形式就这样就ok了

/*

ID           PId         Mark        Porder      Name

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

1            0           *           1           Name1

3            1           *.*         1           Name3

18           1           *.*         2           Name18

15           18          *.*.*       1           Name15

5            15          *.*.*.*.*   1           Name5

11           5           *.*.*.*.*.* 1           Name11

10           5           *.*.*.*.*.* 2           Name10

6            15          *.*.*.*.*   2           Name6

7            15          *.*.*.*.*   3           Name7

8            15          *.*.*.*.*   4           Name8

20           15          *.*.*.*.*   5           Name20

12           1           *.*         3           Name12

2            12          *.*.*       1           Name2

13           2           *.*.*       1           Name13

4            13          *.*.*       1           Name4

*/

叶子 2012-01-08

打赏
举报



declare @T table (Id int,PId int,Mark varchar(11),Porder int,Name varchar(6))

insert into @T

select 11,5,'*.*.*.*.*.*',1,'Name11' union all

select 12,1,'*.*',3,'Name12' union all

select 13,2,'*.*.*',1,'Name13' union all

select 18,1,'*.*',2,'Name18' union all

select 15,18,'*.*.*',1,'Name15' union all

select 3,1,'*.*',1,'Name3' union all

select 1,0,'*',1,'Name1' union all

select 2,12,'*.*.*',1,'Name2' union all

select 4,13,'*.*.*',1,'Name4' union all

select 5,15,'*.*.*.*.*',1,'Name5' union all

select 6,15,'*.*.*.*.*',2,'Name6' union all

select 7,15,'*.*.*.*.*',3,'Name7' union all

select 8,15,'*.*.*.*.*',4,'Name8' union all

select 20,15,'*.*.*.*.*',5,'Name20' union all

select 10,5,'*.*.*.*.*.*',2,'Name10'



DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))

DECLARE @Level int SET @Level=0

INSERT @t_Level SELECT ID,@Level,ID FROM @T WHERE PID =0

WHILE @@ROWCOUNT>0

BEGIN

    SET @Level=@Level+1

    INSERT @t_Level SELECT a.ID,@Level,b.Sort+ltrim(a.porder)+ltrim(a.ID)

    FROM @T a,@t_Level b WHERE a.PID=b.ID AND b.Level=@Level-1

END



SELECT ID=SPACE(b.Level*2)+'|--'+ltrim(a.Id),PId,Mark,Porder,[Name]--,b.Level,b.Sort

FROM @T a,@t_Level b WHERE a.ID=b.ID ORDER BY b.Sort



/*

ID              PId         Mark        Porder      Name

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

|--1            0           *           1           Name1

  |--3          1           *.*         1           Name3

  |--18         1           *.*         2           Name18

    |--15       18          *.*.*       1           Name15

      |--5      15          *.*.*.*.*   1           Name5

        |--11   5           *.*.*.*.*.* 1           Name11

        |--10   5           *.*.*.*.*.* 2           Name10

      |--6      15          *.*.*.*.*   2           Name6

      |--7      15          *.*.*.*.*   3           Name7

      |--8      15          *.*.*.*.*   4           Name8

      |--20     15          *.*.*.*.*   5           Name20

  |--12         1           *.*         3           Name12

    |--2        12          *.*.*       1           Name2

      |--13     2           *.*.*       1           Name13

        |--4    13          *.*.*       1           Name4

*/

jiajiaren 2012-01-08

打赏
举报

[Quote=引用 1 楼 maco_wang 的回复:]

但Id为18的记录底下有子节点，所以先排Id为18的子节点?
ID为12的下面也有子节点呀？
2 12 *.*.* 1 Name2 就是呀？
[/Quote]

因为有 Porder ，Id为18的Porder=2而Id为12的Porder=3，所以Id为18的子节点先排

叶子 2012-01-08

打赏
举报



declare @T table (Id int,PId int,Mark varchar(11),Porder int,Name varchar(6))

insert into @T

select 11,5,'*.*.*.*.*.*',1,'Name11' union all

select 12,1,'*.*',3,'Name12' union all

select 13,2,'*.*.*',1,'Name13' union all

select 18,1,'*.*',2,'Name18' union all

select 15,18,'*.*.*',1,'Name15' union all

select 3,1,'*.*',1,'Name3' union all

select 1,0,'*',1,'Name1' union all

select 2,12,'*.*.*',1,'Name2' union all

select 4,13,'*.*.*',1,'Name4' union all

select 5,15,'*.*.*.*.*',1,'Name5' union all

select 6,15,'*.*.*.*.*',2,'Name6' union all

select 7,15,'*.*.*.*.*',3,'Name7' union all

select 8,15,'*.*.*.*.*',4,'Name8' union all

select 20,15,'*.*.*.*.*',5,'Name20' union all

select 10,5,'*.*.*.*.*.*',2,'Name10'



DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))

DECLARE @Level int

SET @Level=0

INSERT @t_Level SELECT ID,@Level,ID

FROM @T

WHERE PID =0

WHILE @@ROWCOUNT>0

BEGIN

    SET @Level=@Level+1

    INSERT @t_Level SELECT a.ID,@Level,b.Sort+ltrim(a.ID)

    FROM @T a,@t_Level b

    WHERE a.PID=b.ID

        AND b.Level=@Level-1

END

--显示结果

SELECT ID=SPACE(b.Level*2)+'|--'+ltrim(a.Id),PId,Mark,Porder,[Name]

FROM @T a,@t_Level b

WHERE a.ID=b.ID ORDER BY b.Sort



/*

ID                PId         Mark        Porder      Name

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

|--1              0           *           1           Name1

  |--12           1           *.*         3           Name12

    |--2          12          *.*.*       1           Name2

      |--13       2           *.*.*       1           Name13

        |--4      13          *.*.*       1           Name4

  |--18           1           *.*         2           Name18

    |--15         18          *.*.*       1           Name15

      |--20       15          *.*.*.*.*   5           Name20

      |--5        15          *.*.*.*.*   1           Name5

        |--10     5           *.*.*.*.*.* 2           Name10

        |--11     5           *.*.*.*.*.* 1           Name11

      |--6        15          *.*.*.*.*   2           Name6

      |--7        15          *.*.*.*.*   3           Name7

      |--8        15          *.*.*.*.*   4           Name8

  |--3            1           *.*         1           Name3

*/

刚才类型转换我弄差了，修正一下

叶子 2012-01-08

打赏
举报



declare @T table (Id int,PId int,Mark varchar(11),Porder int,Name varchar(6))

insert into @T

select 11,5,'*.*.*.*.*.*',1,'Name11' union all

select 12,1,'*.*',3,'Name12' union all

select 13,2,'*.*.*',1,'Name13' union all

select 18,1,'*.*',2,'Name18' union all

select 15,18,'*.*.*',1,'Name15' union all

select 3,1,'*.*',1,'Name3' union all

select 1,0,'*',1,'Name1' union all

select 2,12,'*.*.*',1,'Name2' union all

select 4,13,'*.*.*',1,'Name4' union all

select 5,15,'*.*.*.*.*',1,'Name5' union all

select 6,15,'*.*.*.*.*',2,'Name6' union all

select 7,15,'*.*.*.*.*',3,'Name7' union all

select 8,15,'*.*.*.*.*',4,'Name8' union all

select 20,15,'*.*.*.*.*',5,'Name20' union all

select 10,5,'*.*.*.*.*.*',2,'Name10'



DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))

DECLARE @Level int

SET @Level=0

INSERT @t_Level SELECT ID,@Level,ID

FROM @T

WHERE PID =0

WHILE @@ROWCOUNT>0

BEGIN

    SET @Level=@Level+1

    INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID

    FROM @T a,@t_Level b

    WHERE a.PID=b.ID

        AND b.Level=@Level-1

END



--显示结果

SELECT ID=SPACE(b.Level*2)+'|--'+ltrim(a.Id),PId,Mark,Porder,[Name]

FROM @T a,@t_Level b

WHERE a.ID=b.ID ORDER BY b.Sort



/*

ID                  PId         Mark        Porder      Name

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

|--1                0           *           1           Name1

  |--12             1           *.*         3           Name12

    |--2            12          *.*.*       1           Name2

  |--18             1           *.*         2           Name18

      |--13         2           *.*.*       1           Name13

        |--4        13          *.*.*       1           Name4

    |--15           18          *.*.*       1           Name15

      |--5          15          *.*.*.*.*   1           Name5

  |--3              1           *.*         1           Name3

      |--6          15          *.*.*.*.*   2           Name6

      |--7          15          *.*.*.*.*   3           Name7

      |--8          15          *.*.*.*.*   4           Name8

        |--10       5           *.*.*.*.*.* 2           Name10

        |--11       5           *.*.*.*.*.* 1           Name11

      |--20         15          *.*.*.*.*   5           Name20

*/