以层次结构形式显示数据 [英] show data in hierarchy form

问题描述

hi
这是我的演示表

hi
this is my demo table

USE [chk]
GO
/****** Object:  Table [dbo].[Demo]    Script Date: 12/05/2011 15:03:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Demo](
	[AC_ID] [bigint] NOT NULL,
	[RootId] [bigint] NULL,
	[parentID] [bigint] NULL,
	[Type] [varchar](50) NULL,
	[Name] [varchar](50) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

这是我的演示数据

this is my demo data

INSERT INTO [demo] VALUES(11,2,9,'S','SubSubGroup22')
INSERT INTO [demo] VALUES(12,3,3,'S','SubGroup31')
INSERT INTO [demo] VALUES(13,4,4,'S','SubGroup41')
INSERT INTO [demo] VALUES(14,3,12,'S','SubSubGroup31')
INSERT INTO [demo] VALUES(15,4,13,'S','SubSubGroup41')
INSERT INTO [demo] VALUES(16,3,14,'S','SubSubGroup32')
INSERT INTO [demo] VALUES(17,1,5,'A','AC11')
INSERT INTO [demo] VALUES(18,1,5,'A','AC12')
INSERT INTO [demo] VALUES(19,1,7,'A','AC13')
INSERT INTO [demo] VALUES(20,1,8,'A','AC14')
INSERT INTO [demo] VALUES(21,2,9,'A','AC21')
INSERT INTO [demo] VALUES(22,2,9,'A','AC22')
INSERT INTO [demo] VALUES(23,2,11,'A','AC23')
INSERT INTO [demo] VALUES(24,2,11,'A','AC24')

这是我的程序

and this is my procedure

ALTER PROC [dbo].[ShowHierarchy]
@Root int



as

	DECLARE @ACID int, @ACName varchar(500),@ACType varchar(50),@ACType1 varchar(50)

	SET @ACName = (SELECT [Name]  FROM  dbo.Demo WHERE  AC_ID= @Root)
	SET @ACType = (SELECT  [Type] FROM dbo.Demo WHERE  AC_ID= @Root)
	SET @ACType1 = (SELECT  [Type] FROM dbo.Demo WHERE  AC_ID= @Root)
	print REPLICATE('-', @@NESTLEVEL * 4) + @ACName+'--'  --+  @root
	SET @ACID = (SELECT MIN(AC_ID) FROM Demo WHERE  parentID= @Root)


	WHILE @ACID IS NOT NULL
	BEGIN
		EXEC dbo.ShowHierarchy @ACID
		SET @ACID = (SELECT MIN(AC_ID) FROM Demo WHERE  parentID= @Root  AND AC_ID > @ACID )
	END

当我运行此过程时
喜欢

--exec ShowHierarchy 1


所以我得到这样的结果

whn i run this procedure
like

--exec ShowHierarchy 1


so i getting result like this

----Group1--
--------SubGroup11--
------------SubSubGroup11--
----------------AC13--
------------SubSubGroup12--
----------------AC14--
------------AC11--
------------AC12--
--------SubGroup12--

但我想要这样的结果

but i want result like that

----Group1--
--------SubGroup11--
------------AC11--
------------AC12--
------------SubSubGroup11--
----------------AC13--
------------SubSubGroup12--
----------------AC14--
--------SubGroup12--

请帮我....

Plese Help Me....

推荐答案

请尝试解决此问题，如果您未按名称订购


ALTER过程[dbo].[ShowHierarchy]
@Root int
如

宣告@ACID int，@ACName varchar(500)，@ ACType varchar(50)，@ ACType1 varchar(50)

选择@ACName =名称，@ ACType = [类型]
来自dbo.Demo，其中AC_ID = @Root

打印REPLICATE(''-''，@@ NESTLEVEL * 4)+ @ACName +''-''; -+ @root

SET @ACID =(从演示WHERE父ID中选择前1个AC_ID = @按名称的根顺序)
从dbo.Demo中选择@ACName = Name，@ ACType = [Type]，其中AC_ID = @ACID

@ACID不为空
开始
EXEC dbo.ShowHierarchy @ACID
SET @ACID =(从演示WHERE parentID中选择前1个AC_ID = @Root AND Name> @ACName按名称顺序)
从dbo.Demo中选择@ACName = Name，@ ACType = [Type]，其中AC_ID = @ACID
END

try this problem was you weren''t ordering by Name


ALTER procedure [dbo].[ShowHierarchy]
@Root int
as

DECLARE @ACID int, @ACName varchar(500),@ACType varchar(50),@ACType1 varchar(50)

Select @ACName = Name, @ACType =[Type]
from dbo.Demo where AC_ID = @Root

print REPLICATE(''-'', @@NESTLEVEL * 4) + @ACName+''--''; --+ @root

SET @ACID = (SELECT Top 1 AC_ID FROM Demo WHERE parentID = @Root Order by Name)
Select @ACName = Name, @ACType =[Type] From dbo.Demo where AC_ID = @ACID

WHILE @ACID IS NOT NULL
BEGIN
EXEC dbo.ShowHierarchy @ACID
SET @ACID = (SELECT Top 1 AC_ID FROM Demo WHERE parentID = @Root AND Name > @ACName Order by Name)
Select @ACName = Name, @ACType =[Type] From dbo.Demo where AC_ID = @ACID
END

我会使用CTE来避免WHILE循环:

I would use a CTE avoiding a WHILE loop:

;WITH cteHierarchy AS
(
	SELECT d.AC_ID
			,d.parentID
			,d.RootId,CAST(N'----' + d.Name + N'--' AS NVARCHAR(MAX)) AS name
			,CAST(d.Name AS NVARCHAR(MAX))+N'/' as lineage
			,CAST(0 AS INT) as lvl
	from dbo.Demo d 
	where d.parentID=0
	UNION ALL
	SELECT d.AC_ID
			,c.AC_ID
			,d.RootId,REPLICATE (N'-',lvl*4+8) + CAST(d.Name AS NVARCHAR(MAX)) + N'--'
			,c.lineage + d.Name + N'/'
			,c.lvl+1
	FROM dbo.Demo d 
	INNER JOIN cteHierarchy c
	ON d.parentID=c.AC_ID
)
SELECT c.name
from cteHierarchy c
order by lineage

这篇关于以层次结构形式显示数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！