-测试数据 DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10)) INSERT @t SELECT '001',NULL ,'山东省' UNION ALL SELECT '002','001','烟台市' UNION ALL SELECT '004','002','招远市' UNION ALL SELECT '003','001','青岛市' UNION ALL SELECT '005',NULL ,'四会市' UNION ALL SELECT '006','005','清远市' UNION ALL SELECT '007','006','小分市'--广度排序显示处理 --生成每个节点的层次数据 DECLARE @t_Level TABLE(ID char(3),Level int) DECLARE @Level int SET @Level=0 INSERT @t_Level SELECT ID,@Level FROM @t WHERE PID IS NULL WHILE @@ROWCOUNT>0 BEGIN SET @Level=@Level+1 INSERT @t_Level SELECT a.ID,@Level FROM @t a,@t_Level b WHERE a.PID=b.ID AND b.Level=@Level-1 END--显示结果 SELECT a.* FROM @t a,@t_Level b WHERE a.ID=b.ID ORDER BY b.Level,b.ID /*--结果 ID PID Name ------- --------- ---------- 001 NULL 山东省 005 NULL 四会市 002 001 烟台市 003 001 青岛市 006 005 清远市 004 002 招远市 007 006 小分市 --*/
邹老大的两个列子 用函数实现: --测试数据 CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10)) INSERT tb SELECT '001',NULL ,'山东省' UNION ALL SELECT '002','001','烟台市' UNION ALL SELECT '004','002','招远市' UNION ALL SELECT '003','001','青岛市' UNION ALL SELECT '005',NULL ,'四会市' UNION ALL SELECT '006','005','清远市' UNION ALL SELECT '007','006','小分市' GO--广度搜索排序函数 CREATE FUNCTION f_Sort(@ID char(3)=NULL,@sort int=1) RETURNS @t_Level TABLE(ID char(3),sort int) AS BEGIN DECLARE tb CURSOR LOCAL FOR SELECT ID FROM tb WHERE PID=@ID OR(@ID IS NULL AND PID IS NULL) OPEN TB FETCH tb INTO @ID WHILE @@FETCH_STATUS=0 BEGIN INSERT @t_Level VALUES(@ID,@sort) SET @sort=@sort+1 IF @@NESTLEVEL<32 --如果递归层数未超过32层(递归最大允许32层) BEGIN --递归查找当前节点的子节点 INSERT @t_Level SELECT * FROM f_Sort(@ID,@sort) SET @sort=@sort+@@ROWCOUNT --排序号加上子节点个数 END FETCH tb INTO @ID END RETURN END GO--显示结果 SELECT a.* FROM tb a,f_Sort(DEFAULT,DEFAULT) b WHERE a.ID=b.ID ORDER BY b.sort /*--结果 ID PID Name ------ --------- ---------- 001 NULL 山东省 002 001 烟台市 004 002 招远市 003 001 青岛市 005 NULL 四会市 006 005 清远市 007 006 小分市 --*/
CREATE TABLE T ( ID INT, PID INT, TITLE VARCHAR(20) )INSERT INTO T SELECT 1,0,'abc' UNION ALL SELECT 2,1,'def' UNION ALL SELECT 3,0,'ghi' UNION ALL SELECT 4,3,'mnq' UNION ALL SELECT 5,1,'rst' UNION ALL SELECT 6,3,'xyz' GOCREATE FUNCTION F_AILAOPO() RETURNS @t TABLE(ID INT, PID INT, TITLE VARCHAR(20), LEVEL INT, BIAO INT ) AS BEGIN DECLARE @i INT SET @i = 0 INSERT INTO @t SELECT *,@i,(SELECT COUNT(1) FROM (SELECT * FROM T A WHERE NOT EXISTS ( SELECT * FROM T B WHERE B.ID = A.PID)) B WHERE A.ID>B.ID) FROM ( SELECT * FROM T A WHERE NOT EXISTS ( SELECT * FROM T B WHERE B.ID = A.PID) ) A WHILE @@ROWCOUNT<>0 BEGIN SET @i = @i + 1 INSERT INTO @t SELECT B.ID,B.PID,B.TITLE,@i,A.BIAO FROM @t A, T B WHERE A.ID = B.PID AND A.LEVEL = @i - 1 END RETURN END GO
SELECT ID,PID,TITLE FROM DBO.F_AILAOPO() ORDER BY BIAO,LEVEL,ID
DROP FUNCTION F_AILAOPO DROP TABLE t ID PID TITLE ----------- ----------- -------------------- 1 0 abc 2 1 def 5 1 rst 3 0 ghi 4 3 mnq 6 3 xyz(所影响的行数为 6 行)
create table #a(id int,pid int,title varchar(10))
insert #a select
1,0,'abc' insert #a select
2,1,'def' insert #a select
3,0,'ghi' insert #a select
4,2,'mnq' insert #a select
5,1,'rst' insert #a select
6,2,'xyz'
select * from #a order by (case when pid=1 then pid else id end)
1 0 abc
2 1 def
5 1 rst
3 0 ghi
4 2 mnq
6 2 xyz
4 2 mnq
6 2 xyz
为什么不排在
2 1 def
下头呢?
-------------------------
1 0 abc
2 1 def
5 1 rst
3 0 ghi
4 3 mnq
6 3 xyz
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'--广度排序显示处理
--生成每个节点的层次数据
DECLARE @t_Level TABLE(ID char(3),Level int)
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT a.*
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Level,b.ID
/*--结果
ID PID Name
------- --------- ----------
001 NULL 山东省
005 NULL 四会市
002 001 烟台市
003 001 青岛市
006 005 清远市
004 002 招远市
007 006 小分市
--*/
用函数实现:
--测试数据
CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))
INSERT tb SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'
GO--广度搜索排序函数
CREATE FUNCTION f_Sort(@ID char(3)=NULL,@sort int=1)
RETURNS @t_Level TABLE(ID char(3),sort int)
AS
BEGIN
DECLARE tb CURSOR LOCAL
FOR
SELECT ID FROM tb
WHERE PID=@ID
OR(@ID IS NULL AND PID IS NULL)
OPEN TB
FETCH tb INTO @ID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @t_Level VALUES(@ID,@sort)
SET @sort=@sort+1
IF @@NESTLEVEL<32 --如果递归层数未超过32层(递归最大允许32层)
BEGIN
--递归查找当前节点的子节点
INSERT @t_Level SELECT * FROM f_Sort(@ID,@sort)
SET @sort=@sort+@@ROWCOUNT --排序号加上子节点个数
END
FETCH tb INTO @ID
END
RETURN
END
GO--显示结果
SELECT a.*
FROM tb a,f_Sort(DEFAULT,DEFAULT) b
WHERE a.ID=b.ID
ORDER BY b.sort
/*--结果
ID PID Name
------ --------- ----------
001 NULL 山东省
002 001 烟台市
004 002 招远市
003 001 青岛市
005 NULL 四会市
006 005 清远市
007 006 小分市
--*/
(
ID INT,
PID INT,
TITLE VARCHAR(20)
)INSERT INTO T
SELECT 1,0,'abc' UNION ALL
SELECT 2,1,'def' UNION ALL
SELECT 3,0,'ghi' UNION ALL
SELECT 4,3,'mnq' UNION ALL
SELECT 5,1,'rst' UNION ALL
SELECT 6,3,'xyz'
GOCREATE FUNCTION F_AILAOPO()
RETURNS @t TABLE(ID INT, PID INT, TITLE VARCHAR(20), LEVEL INT, BIAO INT )
AS
BEGIN
DECLARE @i INT
SET @i = 0
INSERT INTO @t
SELECT *,@i,(SELECT COUNT(1) FROM (SELECT * FROM T A WHERE NOT EXISTS ( SELECT * FROM T B WHERE B.ID = A.PID)) B WHERE A.ID>B.ID)
FROM
(
SELECT * FROM T A WHERE NOT EXISTS ( SELECT * FROM T B WHERE B.ID = A.PID)
) A WHILE @@ROWCOUNT<>0
BEGIN
SET @i = @i + 1
INSERT INTO @t
SELECT B.ID,B.PID,B.TITLE,@i,A.BIAO FROM @t A, T B WHERE A.ID = B.PID AND A.LEVEL = @i - 1
END
RETURN
END
GO
SELECT ID,PID,TITLE FROM DBO.F_AILAOPO() ORDER BY BIAO,LEVEL,ID
DROP FUNCTION F_AILAOPO
DROP TABLE t
ID PID TITLE
----------- ----------- --------------------
1 0 abc
2 1 def
5 1 rst
3 0 ghi
4 3 mnq
6 3 xyz(所影响的行数为 6 行)