请大家帮忙。

[HappySummer]

我想要SELECT一个分层的数据表，就是说，一个部门下设若干子部门，成树状结构，如何选出某个部门及其下属所有部门。（每个部门下属部门数量及层数不等）

[CCEO]

找精华贴
《 化解32层以上嵌套，树型结构的递归实现方法，给大家参考一下！ 》（CrazyFor ）

csdn怎么搞的，精华贴都给转移得不见了

是不是不想发工资了

本ceo决定取消这个月的所有奖金。

[happydreamer]

example

declare @a table (TC_Id int,TC_PID int,TC_Name varchar(200))
insert @a values(1,0,'中国')
insert @a values(2,0,'美国')
insert @a values(3,0,'加拿大')
insert @a values(4,1,'北京')
insert @a values(5,1,'上海')
insert @a values(6,1,'江苏')
insert @a values(7,6,'苏州')
insert @a values(8,7,'常熟')
insert @a values(9,6,'南京')
insert @a values(10,6,'无锡')
insert @a values(11,2,'纽约')
insert @a values(12,2,'旧金山')


declare @lev int,@root int
declare @tmp table (TC_Id int)
declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200),lev int)
insert @tmp select TC_Id from @a where tc_pid=0

while exists (select 1 from @tmp)
begin
set @root=(select top 1 tc_id from @tmp)
delete @tmp where tc_id=@root
set @lev=0
insert @tmp1 select *,@lev from @a where tc_ID=@root
while exists(select 1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1))
begin
set @lev=@lev+1
insert @tmp1 select a.*,@lev from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1)
end
end

select REPLICATE(' ',lev)+tc_name from @tmp1

[lansquenet]

转蚂蚁的精华贴
<化解32层以上嵌套，树型结构的递归实现方法，给大家参考一下>（CrazyFor ）

前一镇，写了两个递归的树型结构处理函数：
http://expert.csdn.net/Expert/topic/1343/1343007.xml?temp=.730694

后来有朋友提出32层以上嵌套的一个展BOM的实例，没有办法用递归实现，特想了个办法，实现32层以上树型结构的递归方法。现在特将以前的那个函数进行了改进，具体如下：

---------------------------------表及函数脚本

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TreeClass]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[TreeClass]
GO

CREATE TABLE [dbo].[TreeClass] (
[TC_id] [int] IDENTITY (1, 1) NOT NULL ,
[TC_PID] [int] NOT NULL ,
[TC_OtherTypeID] [varchar] (8000) COLLATE Chinese_PRC_CI_AS NULL ,
[TC_Name] [varchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TreeClass] WITH NOCHECK ADD
CONSTRAINT [PK_TreeClass] PRIMARY KEY CLUSTERED
(
[TC_id]
) ON [PRIMARY]
GO


if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FN_32GetSubClass]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FN_32GetSubClass]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FN_32GetTopClass]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[FN_32GetTopClass]
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION FN_32GetSubClass (@InputId int,@IdStr varchar(8000)='',@LevelCount int=-1)
/*
参数： @InputId，被搜索子类的ID
@IdStr，一个特殊参数，用于在递归中传数据，注意：调用函数时一定要传入‘’空值
@LevelCount 用于判断是不是递归调用的开始层
*/
RETURNS Varchar(8000)
AS
BEGIN

Declare @TC_ID int,@TC_PID int,@StartLevel int,@Id32 int

if @LevelCount=-1
begin
set @StartLevel=@@NESTLEVEL
set @LevelCount=@StartLevel
end
else
set @StartLevel=-1


If @IdStr='' Set @IdStr=''''+cast(@InputId as varchar)+''''

DECLARE TreeClass CURSOR local FOR --定义游标
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_PID=@InputId

OPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PID

WHILE @@FETCH_STATUS = 0 --循环游标，即循环当前类的弟一级子类
BEGIN
select @IdStr=@IdStr+','+''''+cast(@tC_ID as varchar)+''''

if @@NESTLEVEL<32
set @IdStr=dbo.FN_32GetSubClass (@TC_ID,@IdStr,@LevelCount) --递归，自己调用自己。
else
set @IdStr='['+cast(@tC_ID as varchar)+']'+@IdStr


FETCH NEXT FROM TreeClass
INTO @tC_ID,@TC_PID

End

CLOSE TreeClass
DEALLOCATE TreeClass

while @StartLevel=@@NESTLEVEL and charindex(']',@IdStr)>0
begin
set @Id32=substring(@IdStr,2,charindex(']',@IdStr)-2)
set @IdStr=dbo.FN_32GetSubClass (@Id32,@IdStr,@LevelCount)
set @IdStr=replace(@IdStr,'['+cast(@Id32 as varchar)+']','')
end

Return @IdStr

END







GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION FN_32GetTopClass (@InputId int,@IdStr varchar(8000)='',@type int=0,@LevelCount int=-1)

RETURNS Varchar(8000)
AS
BEGIN

Declare @TC_ID int,@TC_PID int,@StartLevel int,@Id32 int


if @LevelCount=-1
begin
set @StartLevel=@@NESTLEVEL
set @LevelCount=@StartLevel
end
else
set @StartLevel=-1

DECLARE TreeClass CURSOR local FOR
SELECT TC_Id,TC_PID
FROM TreeClass
where TC_ID=@InputId

OPEN TreeClass
FETCH NEXT FROM TreeClass
INTO @TC_ID,@TC_PID

WHILE @@FETCH_STATUS = 0
BEGIN
if @type=1
begin
if @IdStr<>'' select @IdStr=','+@IdStr
select @IdStr=''''+cast(@tC_ID as varchar)+''''+@IdStr
end
else
if @TC_PID=0 select @IdStr=cast(@tC_ID as varchar)

if @@NESTLEVEL<32
select @IdStr=dbo.FN_32GetTopClass (@TC_PID,@IdStr,@type,@LevelCount)
else
set @IdStr=@IdStr+'['+cast(@tC_ID as varchar)+']'


FETCH NEXT FROM TreeClass
INTO @tC_ID,@TC_PID

End

CLOSE TreeClass
DEALLOCATE TreeClass

while @StartLevel=@@NESTLEVEL and charindex(']',@IdStr)>0
begin
set @Id32=substring(@IdStr,charindex('[',@Idstr)+1,charindex(']',@IdStr)-1-charindex('[',@Idstr))
set @IdStr=dbo.FN_32GetTopClass (@Id32,@IdStr,@type,@LevelCount)
set @IdStr=replace(@IdStr,'['+cast(@Id32 as varchar)+']','')
end

Return @IdStr

END




GO
SET QUOTED_IDENTIFIER OFF
GO
SET
---------------------------------------------------------------

[伍子V5]

要找的是这个吗
http://expert.csdn.net/Expert/topic/2285/2285830.xml?temp=.1570551

/*--树形数据处理方案

树形数据的排序,新增,修改,复制,删除,数据完整性检查,汇总统计
--邹建 2003.9--*/

[pengdali]

/*--按父找子--*/
declare @a table (TC_Id int,TC_PID int,TC_Name varchar(200))
insert @a values(1,0,'中国')
insert @a values(2,0,'美国')
insert @a values(3,0,'加拿大')
insert @a values(4,1,'北京')
insert @a values(5,1,'上海')
insert @a values(6,1,'江苏')
insert @a values(7,6,'苏州')
insert @a values(8,7,'常熟')
insert @a values(9,6,'南京')
insert @a values(10,6,'无锡')
insert @a values(11,2,'纽约')
insert @a values(12,2,'旧金山')

declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200),lev int)
insert @tmp1 select *,1 from @a where tc_ID=1
while exists(select 1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1))
insert @tmp1 select a.*,1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1)
select * from @tmp1

/*--按子找父--*/
--建立环境
declare @a table (TC_Id int,TC_PID int,TC_Name varchar(200))
insert @a values(1,0,'中国')
insert @a values(2,0,'美国')
insert @a values(3,0,'加拿大')
insert @a values(4,1,'北京')
insert @a values(5,1,'上海')
insert @a values(6,1,'江苏')
insert @a values(7,6,'苏州')
insert @a values(8,7,'常熟')
insert @a values(9,6,'南京')
insert @a values(10,6,'无锡')
insert @a values(11,2,'纽约')
insert @a values(12,2,'旧金山')

declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200))

--开始结点
insert @tmp1 select * from @a where tc_ID=10

--循环得到
while exists(select 1 from @a a,@tmp1 b where a.tc_id=b.tc_pID and a.tc_ID not in (select tc_ID from @tmp1))
insert @tmp1 select a.* from @a a,@tmp1 b where a.tc_id=b.tc_pID and a.tc_ID not in (select tc_ID from @tmp1)

--显示结果
select * from @tmp1

/*--遍厉所有--*/
declare @a table (TC_Id int,TC_PID int,TC_Name varchar(200))
insert @a values(1,0,'中国')
insert @a values(2,0,'美国')
insert @a values(3,0,'加拿大')
insert @a values(4,1,'北京')
insert @a values(5,1,'上海')
insert @a values(6,1,'江苏')
insert @a values(7,6,'苏州')
insert @a values(8,7,'常熟')
insert @a values(9,6,'南京')
insert @a values(10,6,'无锡')
insert @a values(11,2,'纽约')
insert @a values(12,2,'旧金山')

declare @tmp1 table (TC_Id int,TC_PID int,TC_Name varchar(200))
while exists(select 1 from @a where TC_PID=0 and tc_id not in (select tc_id from @tmp1))
begin
insert @tmp1 select top 1 * from @a where TC_PID=0 and tc_id not in (select tc_id from @tmp1) order by TC_Id
while exists(select 1 from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1))
insert @tmp1 select a.* from @a a,@tmp1 b where a.tc_pid=b.tc_ID and a.tc_ID not in (select tc_ID from @tmp1)
end
select * from @tmp1

[lbzq]

将数据库内容读到一个dataset中，然后做深度优先遍历。ok？

[lbzq]

有问题现场指导。
嘿嘿

[yujohny]

CSDN怎么没保存精华贴的地方