select id1=identity(int,1,1),* into temp from
(
select id , diqu , px = 1 from tb where sign = 0
union all
select a.sign id,a.diqu , px = 2 from tb a,tb b where a.id = b.sign
) t
order by px,idselect id1 id , diqu from temp order by id1
(
select id , diqu , px = 1 from tb where sign = 0
union all
select a.sign id,a.diqu , px = 2 from tb a,tb b where a.id = b.sign
) t
order by px,idselect id1 id , diqu from temp order by id1
參考:http://topic.csdn.net/t/20030120/12/1375432.html
insert into t4 select 1 , '黑龙江省', 0
insert into t4 select 2 , '河北省', 0
insert into t4 select 3 , '吉林省' , 0
insert into t4 select 4 , '牡丹江市' , 1
insert into t4 select 5 , '哈尔滨市' , 1
insert into t4 select 6 , '保定市' , 2
insert into t4 select 7 , '长春市' , 3
insert into t4 select 8 , '河南省' , 0
insert into t4 select 9 , '郑州市' , 8
select * from
(
select id , diqu , px = 1 from t4 where sign = 0
union all
select a.id,b.diqu , px = 2 from t4 a,t4 b where a.id = b.sign
) t
order by id, px
select id,diqu --,case when isnull(nullif(sign,0),id)=id then 0 else 1 end level, isnull(nullif(sign,0),id) gid
from tb order by isnull(nullif(sign,0),id),case when isnull(nullif(sign,0),id)=id then 0 else 1 end
比如
1 黑龙江省 1
2 河北省 2
3 吉林省 3
4 牡丹江市 1
5 哈尔滨市 1
6 保定市 2
7 长春市 3
8 河南省 8
9 郑州市 8 那么语句写法会更简单select * from tb order by sign,nullif(id,sign)我几年前做个二级分类的论坛,数据就是这么定义的.
(
id int,
diqu VARCHAR(20),
[sign] INT
)
INSERT INTO T
SELECT 1,'黑龙江省',0 UNION ALL
SELECT 2,'河北省',0 UNION ALL
SELECT 3,'吉林省',0 UNION ALL
SELECT 4,'牡丹江市',1 UNION ALL
SELECT 5,'哈尔滨市',1 UNION ALL
SELECT 6,'保定市',2 UNION ALL
SELECT 7,'长春市',3 UNION ALL
SELECT 8,'河南省',0 UNION ALL
SELECT 9,'郑州市',8
SELECT B.ID FROM T A,T B WHERE B.id = A.[SIGN] AND A.id = 4
SELECT B.ID FROM T A,T B WHERE B.id = A.[SIGN] AND A.id = 1
GO
CREATE FUNCTION F_TEST(@id INT)
RETURNS INT
AS
BEGIN
DECLARE @i INT
SET @i = 0 WHILE EXISTS ( SELECT * FROM T A ,T B WHERE B.id = A.[SIGN] AND A.id = @id )
SELECT @id = B.id FROM T A,T B WHERE B.id = A.[SIGN] AND A.id = @id
RETURN @id
END
GOSELECT ID,DIQU FROM t ORDER BY DBO.F_TEST(ID),idDROP FUNCTION F_TESTDROP TABLE TID DIQU
----------- --------------------
1 黑龙江省
4 牡丹江市
5 哈尔滨市
2 河北省
6 保定市
3 吉林省
7 长春市
8 河南省
9 郑州市
---改了一下,因为可能如果递归排位的顺序先后不定,那么就不太好排了,比如例子中的4和6的关系
CREATE TABLE T
(
id int,
diqu VARCHAR(20),
[sign] INT
)
INSERT INTO T
SELECT 1,'黑龙江省',0 UNION ALL
SELECT 2,'河北省',0 UNION ALL
SELECT 3,'吉林省',0 UNION ALL
SELECT 4,'菁远县',6 UNION ALL
SELECT 5,'牡丹江市',1 UNION ALL
SELECT 6,'哈尔滨市',1 UNION ALL
SELECT 7,'保定市',2 UNION ALL
SELECT 8,'长春市',3 UNION ALL
SELECT 9,'河南省',0 UNION ALL
SELECT 10,'郑州市',8SELECT B.ID FROM T A,T B WHERE B.id = A.[SIGN] AND A.id = 4
SELECT B.ID FROM T A,T B WHERE B.id = A.[SIGN] AND A.id = 1
GO
CREATE FUNCTION F_TEST(@id INT)
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE @i VARCHAR(100)
SET @i = RIGHT(10000+@id,2) WHILE EXISTS ( SELECT * FROM T A ,T B WHERE B.id = A.[SIGN] AND A.id = @id )
BEGIN
SELECT @id = B.id FROM T A,T B WHERE B.id = A.[SIGN] AND A.id = @id
SET @i = RIGHT(10000+@id,2)+@i
END
RETURN @i
END
GOSELECT ID,DIQU,dbo.F_TEST(ID) FROM t ORDER BY dbo.F_TEST(ID) DROP FUNCTION F_TESTDROP TABLE T
ID DIQU
----------- -------------------- --------------------
1 黑龙江省 01
5 牡丹江市 0105
6 哈尔滨市 0106
4 菁远县 010604
2 河北省 02
7 保定市 0207
3 吉林省 03
8 长春市 0308
10 郑州市 030810
9 河南省 09(所影响的行数为 10 行)
其他的几位也辛苦了,总之多谢大家。
呵呵,看来我还要多加努力。
现在结帖,3Q
10楼的写法是若你sign将0改为id的.不过这种写法只适用级数只有二级的.
多级和无级的要用循环或函数来处理.
select id,diqu,px=1 from t4
where sign=0
union
select a.id,b.diqu ,px=2 from t4 a,t4 b
where a.id=b.sign
order by id,px