求一Sql 树查询语句

netboygg 2013-08-16 04:30:55

现有一张表：

CREATE TABLE [dbo].[controlset](

	[id] [int] IDENTITY(1,1) NOT NULL,

	[parentid] [int] NULL,

	[controlname] [varchar](300) NULL,

	[displayorder] [int] NULL,

 CONSTRAINT [PK_controlset] PRIMARY KEY CLUSTERED 

(

	[id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]



GO

中有如下数据：

INSERT INTO controlset VALUES(0,'a',1)

INSERT INTO controlset VALUES(1,'b',2)

INSERT INTO controlset VALUES(1,'c',3)

INSERT INTO controlset VALUES(1,'d',4)

INSERT INTO controlset VALUES(0,'e',2)

INSERT INTO controlset VALUES(5,'f',1)

INSERT INTO controlset VALUES(1,'g',5)

INSERT INTO controlset VALUES(5,'h',2)

想得到以下结果：
id 父id name 显示顺序
1 0 a 1
2 1 b 1
3 1 c 2
4 1 d 3
7 1 g 4
5 0 e 2
6 5 f 1
8 5 h 2

这是和常见的树不一样的地方
首先，a与e的父节点都是0，其次，需要先把a及其子项或者子项的子项都查出完之后，再查e的子项、子子项。显示顺序是判断a与e的出现先后顺序。列表的结果顺序与id号的大小顺序无关。

谢谢高手了。

...全文

193 7 打赏收藏转发到动态举报

写回复

用AI写文章

7 条回复

切换为时间正序

请发表友善的回复…

发表回复

最爱午夜 2013-08-19

打赏
举报

使用CTE递归遍历层次关系，在微软的示例AdventureWork数据库中 WITH EmployeePath( EmployeeID,ManagerID,LV) AS ( SELECT EmployeeID,ManagerID,1 FROM HumanResources.Employee WHERE ManagerID=109 UNION ALL SELECT A.EmployeeID,A.ManagerID,LV+1 FROM HumanResources.Employee AS A JOIN EmployeePath AS B ON A.ManagerID=B.EmployeeID ) SELECT * FROM EmployeePath

hxm20003 2013-08-19

打赏
举报

先把数据放到一个临时表排序，然后用group。

Shawn 2013-08-17

打赏
举报

--从第二层节点开始递归就对了。递归完，再把根节点加进去，并确定其正确位置。参考：

IF object_id('tempdb..#controlset') IS NOT NULL DROP TABLE #controlset
go
CREATE TABLE #controlset(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [parentid] [int] NULL,
    [controlname] [varchar](300) NULL,
    [displayorder] [int] NULL)  

INSERT INTO #controlset VALUES(0,'a',1)
INSERT INTO #controlset VALUES(1,'b',2)
INSERT INTO #controlset VALUES(1,'c',3)
INSERT INTO #controlset VALUES(1,'d',4)
INSERT INTO #controlset VALUES(0,'e',2)
INSERT INTO #controlset VALUES(5,'f',1)
INSERT INTO #controlset VALUES(1,'g',5)
INSERT INTO #controlset VALUES(5,'h',2)
INSERT INTO #controlset VALUES(3,'h',1)
INSERT INTO #controlset VALUES(3,'h',2)
--INSERT INTO #controlset VALUES(10,'x',10)
--INSERT INTO #controlset VALUES(11,'y',11)
--INSERT INTO #controlset VALUES(6,'z',8)
 
--SELECT * FROM #controlset
--sql:
;WITH cte AS
(
	SELECT 
		nodeid=ROW_NUMBER() OVER(ORDER BY parentid, id),  
		[level] = 1, id, parentid, controlname, displayorder    
	FROM #controlset WHERE parentid IN(SELECT id FROM #controlset WHERE parentid=0)  --从第二层子节点开始递归
	UNION ALL
	SELECT 
		nodeid, [level] = [level]+1, b.id, b.parentid, b.controlname, b.displayorder
	FROM cte a
	INNER JOIN #controlset b
		ON a.id = b.parentid
)
SELECT id,parentid,controlname,displayorder FROM
(
	SELECT nodeid,[level],id,parentid,controlname,displayorder FROM cte
	UNION ALL
	SELECT 
		nodeid=(SELECT TOP(1) b.nodeid FROM cte b WHERE b.parentid = a.id),	--找到根节点的第一个子节点的nodeid
		[level]=0,id,parentid,controlname,displayorder 
	FROM #controlset a WHERE parentid=0	--把根节点UNION ALL进来,并控制好它的nodeid
) t
ORDER BY nodeid,[level], id  --按根节点,节点所在的级别,同级别内按id排序
/*
id	parentid	controlname	displayorder
1	0	a	1
2	1	b	2
3	1	c	3
9	3	h	1
10	3	h	2
4	1	d	4
7	1	g	5
5	0	e	2
6	5	f	1
8	5	h	2
*/

Shawn 2013-08-17

打赏
举报

如果层数和子节点数，不固定，还是洗。这个单纯用CTE不好实现，建议楼主查出数据后，还是在前台实现，根据LEVEL和PARENTID.

netboygg 2013-08-16

打赏
举报

引用 2 楼 wwwwgou 的回复:

IF object_id('tempdb..#controlset') IS NOT NULL DROP TABLE #controlset go CREATE TABLE #controlset( [id] [int] IDENTITY(1,1) NOT NULL, [parentid] [int] NULL, [controlname] [varchar](300) NULL, [displayorder] [int] NULL) INSERT INTO #controlset VALUES(0,'a',1) INSERT INTO #controlset VALUES(1,'b',2) INSERT INTO #controlset VALUES(1,'c',3) INSERT INTO #controlset VALUES(1,'d',4) INSERT INTO #controlset VALUES(0,'e',2) INSERT INTO #controlset VALUES(5,'f',1) INSERT INTO #controlset VALUES(1,'g',5) INSERT INTO #controlset VALUES(5,'h',2) ................................ */

谢谢大虾，但是如果是3层的话顺序就不对了

IF object_id('tempdb..#controlset') IS NOT NULL DROP TABLE #controlset
go
CREATE TABLE #controlset(
    [id] [int] IDENTITY(1,1) NOT NULL,
    [parentid] [int] NULL,
    [controlname] [varchar](300) NULL,
    [displayorder] [int] NULL)  
 
INSERT INTO #controlset VALUES(0,'a',1)
INSERT INTO #controlset VALUES(1,'b',2)
INSERT INTO #controlset VALUES(1,'c',3)
INSERT INTO #controlset VALUES(1,'d',4)
INSERT INTO #controlset VALUES(0,'e',2)
INSERT INTO #controlset VALUES(5,'f',1)
INSERT INTO #controlset VALUES(1,'g',5)
INSERT INTO #controlset VALUES(5,'h',2)
INSERT INTO #controlset VALUES(3,'h',1)
INSERT INTO #controlset VALUES(3,'h',2)

--sql:
;WITH cte AS
(
SELECT 
rootid=ROW_NUMBER() OVER(ORDER BY id),	--根节点id
[level] = 1,*	--节点的级别
FROM #controlset WHERE parentid = 0
UNION ALL
SELECT rootid, [level] = [level]+1, b.*
FROM cte a
INNER JOIN #controlset b
ON a.id = b.parentid
)
SELECT id,parentid,controlname,displayorder FROM cte
ORDER BY rootid, [level], id  --按根节点,节点所在的级别,同级别内按id排序

结果是： 1 0 a 1 2 1 b 2 3 1 c 3 4 1 d 4 7 1 g 5 9 3 h 1 10 3 h 2 5 0 e 2 6 5 f 1 8 5 h 2 正确的应该是 1 0 a 1 2 1 b 2 3 1 c 3 9 3 h 1 10 3 h 2 4 1 d 4 7 1 g 5 5 0 e 2 6 5 f 1 8 5 h 2 是一层一层的显示出子项，如果有子子项的话，先显示出子子项，再显示子项。是我没说清楚。

Shawn 2013-08-16

打赏
举报

IF object_id('tempdb..#controlset') IS NOT NULL DROP TABLE #controlset go CREATE TABLE #controlset( [id] [int] IDENTITY(1,1) NOT NULL, [parentid] [int] NULL, [controlname] [varchar](300) NULL, [displayorder] [int] NULL) INSERT INTO #controlset VALUES(0,'a',1) INSERT INTO #controlset VALUES(1,'b',2) INSERT INTO #controlset VALUES(1,'c',3) INSERT INTO #controlset VALUES(1,'d',4) INSERT INTO #controlset VALUES(0,'e',2) INSERT INTO #controlset VALUES(5,'f',1) INSERT INTO #controlset VALUES(1,'g',5) INSERT INTO #controlset VALUES(5,'h',2) --sql: ;WITH cte AS ( SELECT rootid=ROW_NUMBER() OVER(ORDER BY id), --根节点id [level] = 1,* --节点的级别 FROM #controlset WHERE parentid = 0 UNION ALL SELECT rootid, [level] = [level]+1, b.* FROM cte a INNER JOIN #controlset b ON a.id = b.parentid ) SELECT id,parentid,controlname,displayorder FROM cte ORDER BY rootid, [level], id --按根节点,节点所在的级别,同级别内按id排序 /* id parentid controlname displayorder 1 0 a 1 2 1 b 2 3 1 c 3 4 1 d 4 7 1 g 5 5 0 e 2 6 5 f 1 8 5 h 2 */