递归求部门列表!

[jwt1982]

数据库中有部门表如下,怎么实现得到某部门及其下属所有的部门？
谢谢！
BH NAME UP
01 00001 0000000
02 0002 01
03 0003 01
04 0004 03

[jwt1982]

哈哈，很笨哈哈！
定义问题哈哈！

[xhwly]

給你一個例子(本論壇文章,不記得作者了)

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

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

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



SET IDENTITY_INSERT TreeClass on
Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (1, 0, '', '中國' )
Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (2, 0, '', '美國' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (3, 0, '', '加拿大' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (4, 1, '', '北京' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (5, 1, '', '上海' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (6, 1, '', '江蘇' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (7, 6, '', '蘇州' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (8, 7, '', '常熟' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (9, 6, '', '南京' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (10, 6, '', '無錫' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (11, 2, '', '鈕約' )

Insert into TreeClass (TC_ID,TC_PID,TC_OtherTypeID,TC_Name) values (12, 2, '', '舊金山' )


select * from treeclass


SET IDENTITY_INSERT TreeClass off

Go


SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE FUNCTION FN_GetSubClass (@InputId int,@IdStr varchar(8000))

RETURNS Varchar(8000)
AS
BEGIN

Declare @TC_ID int,@TC_PID int

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)+''''

select @IdStr=dbo.FN_GetSubClass (@TC_ID,@IdStr)


FETCH NEXT FROM TreeClass
INTO @tC_ID,@TC_PID

End

CLOSE TreeClass
DEALLOCATE TreeClass

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_GetTopClass (@InputId int,@IdStr varchar(8000),@type int)

RETURNS Varchar(8000)
AS
BEGIN

Declare @TC_ID int,@TC_PID int

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)

select @IdStr=dbo.FN_GetTopClass (@TC_PID,@IdStr,@type)


FETCH NEXT FROM TreeClass
INTO @tC_ID,@TC_PID

End

CLOSE TreeClass
DEALLOCATE TreeClass

Return @IdStr

END


GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--得到ID?1的所有下???ID串
select dbo.fn_getsubclass(1,'')
--查?ID?1的所有下???
select * from treeclass where charindex(''''+cast(TC_id as varchar)+'''',dbo.fn_getsubclass(1,''))>0
--得到ID?10??ID
select dbo.fn_gettopclass(10,'',0)
--得到提供ID所在枝的所有ID
select dbo.fn_getsubclass(dbo.fn_gettopclass(10,'',0),'')
--得到?前ID到??的ID串
select dbo.fn_gettopclass(10,'',1)

[jwt1982]

CREATE FUNCTION FN_GetSubDepts (@InputId Nvarchar,@IdStr varchar(8000))

RETURNS Varchar(8000)
AS
BEGIN

Declare @TC_ID nvarchar,@TC_PID nvarchar

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

DECLARE RsDept CURSOR local FOR
SELECT bh,up
FROM rsdept
where up=@InputId

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

WHILE @@FETCH_STATUS = 0
BEGIN
select @IdStr=@IdStr+','+''''+cast(@TC_ID as varchar)+''''

select @IdStr=dbo.FN_GetSubrsdepts (@TC_ID,@IdStr)


FETCH NEXT FROM RsDept
INTO @tC_ID,@TC_PID

End

CLOSE RsDept
DEALLOCATE RsDept

Return @IdStr

END
-----------------------------------------------------------------
我的表名是：RSDEPT 上面是我改写的函数，但是不好用，请大大帮帮忙看看！