创建SQL的递归查询 ，急急！！！！

有一张表 company（部门表） 
结构： 
ID(自动递增)、PID(父ID)、Name(名称) 
怎么写个函数实现能查询该部门下所以有子部门的记录。 
列如： 
ID、PID、Name 
1    0  部门1 
2    0  部门2 
3    1  部门1.1 
4    3  部门1.1.1 
5    4  部门1.1.1.1 
6    2  部门2.1 
7    6  部门2.1.1 
8    7  部门2.1.1.1 

查询的时候输入对应的ID的值(可能是一个输入参数，也可能是多个输入参数)，能查询出该ID下面的所以有记录（包括本身的记录）。 
假如：输入1就能查询1、3,4,5所以对应的记录（一个输入参数）、输入1,2就能查询1,2,3,4,5,6,7,8。 
请教各位帮帮忙，能不能写个函数实现这种查询的功能。 

8 个解决方案

#1

--测试数据
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_Cid(@ID char(3))
RETURNS @t_Level TABLE(ID char(3),Level int)
AS
BEGIN
	DECLARE @Level int
	SET @Level=1
	INSERT @t_Level SELECT @ID,@Level
	WHILE @@ROWCOUNT>0
	BEGIN
		SET @Level=@Level+1
		INSERT @t_Level SELECT a.ID,@Level
		FROM tb a,@t_Level b
		WHERE a.PID=b.ID
			AND b.Level=@Level-1
	END
	RETURN
END
GO

--调用函数查询002及其所有子节点
SELECT a.*
FROM tb a,f_Cid('002') b
WHERE a.ID=b.ID
/*--结果
ID   PID  Name       
------ ------- ---------- 
002  001  烟台市
004  002  招远市
--*/

#2

--测试数据
CREATE TABLE tb(ID INT,PID int,Name nvarchar(10))
INSERT tb SELECT 1,0,N'部门1'
UNION ALL SELECT 2,0,N'部门2'
UNION ALL SELECT 3,1,N'部门1.1'
UNION ALL SELECT 4,3,N'部门1.1.1'
UNION ALL SELECT 5,4,N'部门1.1.1.1'
UNION ALL SELECT 6,2,N'部门2.1'
UNION ALL SELECT 7,6,N'部门2.1.1'
UNION ALL SELECT 8,7,N'部门2.1.1.1'
GO

--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID INT)
RETURNS @t_Level TABLE(ID INT,Level int)
AS
BEGIN
	DECLARE @Level int
	SET @Level=1
	INSERT @t_Level SELECT @ID,@Level
	WHILE @@ROWCOUNT>0
	BEGIN
		SET @Level=@Level+1
		INSERT @t_Level SELECT a.ID,@Level
		FROM tb a,@t_Level b
		WHERE a.PID=b.ID
			AND b.Level=@Level-1
	END
	RETURN
END
GO

--调用函数查询节点[ 1 ]及其所有子节点
SELECT a.*
FROM tb a,f_Cid(1) b
WHERE a.ID=b.ID
/*--结果
ID          PID         Name       
----------- ----------- ---------- 
1           0           部门1
3           1           部门1.1
4           3           部门1.1.1
5           4           部门1.1.1.1

(4 row(s) affected)
--*/


DROP FUNCTION F_Cid
DROP TABLE TB

#3

--测试数据
CREATE TABLE tb(ID INT,PID int,Name nvarchar(10))
INSERT tb SELECT 1,0,N'部门1'
UNION ALL SELECT 2,0,N'部门2'
UNION ALL SELECT 3,1,N'部门1.1'
UNION ALL SELECT 4,3,N'部门1.1.1'
UNION ALL SELECT 5,4,N'部门1.1.1.1'
UNION ALL SELECT 6,2,N'部门2.1'
UNION ALL SELECT 7,6,N'部门2.1.1'
UNION ALL SELECT 8,7,N'部门2.1.1.1'
GO

--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID VARCHAR(10))
RETURNS @t_Level TABLE(ID INT,Level int)
AS
BEGIN
	DECLARE @Level int
	DECLARE @PID INT

	SELECT @ID = @ID + ',' , @Level=1
	WHILE CHARINDEX(',',@ID)>0
	BEGIN	
 		SET @PID = CAST(LEFT(@ID,CHARINDEX(',',@ID)-1) AS INT)

		INSERT @t_Level SELECT @PID,@Level
		WHILE @@ROWCOUNT>0
		BEGIN
			SET @Level=@Level+1
			INSERT @t_Level SELECT a.ID,@Level
			FROM tb a,@t_Level b
			WHERE a.PID=b.ID
				AND b.Level=@Level-1
		END
		SET @ID = STUFF(@ID,1,CHARINDEX(',',@ID),'')
	END
	RETURN
END
GO

--调用函数查询节点[ 1 ]及其所有子节点
SELECT a.*
FROM tb a,f_Cid('1,2') b
WHERE a.ID=b.ID
/*--结果
ID          PID         Name       
----------- ----------- ---------- 
1           0           部门1
2           0           部门2
3           1           部门1.1
4           3           部门1.1.1
5           4           部门1.1.1.1
6           2           部门2.1
7           6           部门2.1.1
8           7           部门2.1.1.1

(8 row(s) affected)
--*/


DROP FUNCTION F_Cid
DROP TABLE TB

#4

-->生成测试数据
 
declare @tb table([ID] int,[PID] nvarchar(1),[Name] nvarchar(9))
Insert @tb
select 1,N'0',N'部门1' union all
select 2,N'0',N'部门2' union all
select 3,N'1',N'部门1.1' union all
select 4,N'3',N'部门1.1.1' union all
select 5,N'4',N'部门1.1.1.1' union all
select 6,N'2',N'部门2.1' union all
select 7,N'6',N'部门2.1.1' union all
select 8,N'7',N'部门2.1.1.1'
declare @ID nvarchar(10)
set @ID ='1,2'
Select * from @tb 
where charindex(substring([Name],3,charindex('.',[Name]+'.')-3),ltrim(@ID))>0
/*
ID          PID  Name
----------- ---- ---------
1           0    部门1
2           0    部门2
3           1    部门1.1
4           3    部门1.1.1
5           4    部门1.1.1.1
6           2    部门2.1
7           6    部门2.1.1
8           7    部门2.1.1.1
*/

#5

如果查询的结果集中不包括本身的记录 ，请问3楼又该怎么修改呢？
即：
3           1           部门1.1
4           3           部门1.1.1
5           4           部门1.1.1.1
6           2           部门2.1
7           6           部门2.1.1
8           7           部门2.1.1.1

#6

3楼的语句在调用时SELECT a.*
FROM tb a,f_Cid('1,2') b
WHERE a.ID=b.ID
查询分析报错
将 varchar 值 '1,2' 转换为数据类型为 int 的列时发生语法错误。

#7

--测试数据
CREATE TABLE tb(ID INT,PID int,Name nvarchar(10))
INSERT tb SELECT 1,0,N'部门1'
UNION ALL SELECT 2,0,N'部门2'
UNION ALL SELECT 3,1,N'部门1.1'
UNION ALL SELECT 4,3,N'部门1.1.1'
UNION ALL SELECT 5,4,N'部门1.1.1.1'
UNION ALL SELECT 6,2,N'部门2.1'
UNION ALL SELECT 7,6,N'部门2.1.1'
UNION ALL SELECT 8,7,N'部门2.1.1.1'
GO

--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid(@ID VARCHAR(10))
RETURNS @t_Level TABLE(ID INT,Level int)
AS
BEGIN
    DECLARE @Level int
    DECLARE @PID INT
    
    SELECT @ID = @ID + ',' , @Level=1
    WHILE CHARINDEX(',',@ID)>0
    BEGIN    
         SET @PID = CAST(LEFT(@ID,CHARINDEX(',',@ID)-1) AS INT)

        INSERT @t_Level SELECT @PID,@Level
        WHILE @@ROWCOUNT>0
        BEGIN
            SET @Level=@Level+1
            INSERT @t_Level SELECT a.ID,@Level
            FROM tb a,@t_Level b
            WHERE a.PID=b.ID
                AND b.Level=@Level-1
        END
        SET @ID = STUFF(@ID,1,CHARINDEX(',',@ID),'')
    END
    RETURN
END
GO

--查询指定节点及其所有子节点的函数
CREATE FUNCTION f_Cid_Child(@ID VARCHAR(10))
RETURNS @t_Level TABLE(ID INT,Level int)
AS
BEGIN
    DECLARE @Level int
    DECLARE @PID INT
    
    SELECT @ID = @ID + ',' , @Level=1
    WHILE CHARINDEX(',',@ID)>0
    BEGIN    
         SET @PID = CAST(LEFT(@ID,CHARINDEX(',',@ID)-1) AS INT)

        INSERT @t_Level SELECT @PID,@Level
        WHILE @@ROWCOUNT>0
        BEGIN
            SET @Level=@Level+1
            INSERT @t_Level SELECT a.ID,@Level
            FROM tb a,@t_Level b
            WHERE a.PID=b.ID
                AND b.Level=@Level-1
        END
		DELETE FROM @t_Level WHERE ID=@PID
        SET @ID = STUFF(@ID,1,CHARINDEX(',',@ID),'')
    END
    RETURN
END
GO


declare @str varchar(10)

set @str = '1,2'   --要查找的字符串

--调用函数查询节点[ 1 ]及其所有子节点
SELECT a.*
FROM tb a,f_Cid(@str) b
WHERE a.ID=b.ID
ORDER BY A.Name


--调用函数查询节点[ 1 ]及其所有子节点
SELECT a.*
FROM tb a,f_Cid_Child(@str) b
WHERE a.ID=b.ID
ORDER BY A.Name



/*--结果

ID          PID         Name       
----------- ----------- ---------- 
1           0           部门1
3           1           部门1.1
4           3           部门1.1.1
5           4           部门1.1.1.1
2           0           部门2
6           2           部门2.1
7           6           部门2.1.1
8           7           部门2.1.1.1

(8 row(s) affected)

ID          PID         Name       
----------- ----------- ---------- 
3           1           部门1.1
4           3           部门1.1.1
5           4           部门1.1.1.1
6           2           部门2.1
7           6           部门2.1.1
8           7           部门2.1.1.1

(6 row(s) affected)

--*/


DROP FUNCTION F_Cid,f_Cid_Child
DROP TABLE TB

#8

引用 3 楼 leo_lesley 的回复:

SQL code--测试数据CREATETABLEtb(IDINT,PIDint,Namenvarchar(10))INSERTtbSELECT1,0,N'部门1'UNIONALLSELECT2,0,N'部门2'UNIONALLSELECT3,1,N'部门1.1'UNIONALLSELECT4,3,N'部门1.1.1'UNIONALLSELECT5,4,N'部门1.1.1.1'UNIONALLSELECT6,2,N'部门2.1'UNIONALLSELECT7,6,N'部门2.1.1'UNIONALLSELECT8,7,N'部门2.1.1.1'GO--查询指定节点及其所有子节点的函数CREATEFUNCTIONf_Cid(@IDVARCHAR(10))RETURNS@t_LevelTABLE(IDINT,Leveli…