递归查询

Question

实现递归查询：用函数，存储过程，其他方法（如派生表之类）都可以，最好讲讲效率问题要求：同级按sort排序
数据库设计：id    pid   sort    name
-----------------------------------------------
1     0     1       中国
2     0     2       美国
3     0     3       英国4     1     1       北京
5     1     2       广西
6     1     3       广州
7     1     4       上海8     5     1       南宁
9     5     2       桂林
10    5     3       柳州11    8     1       区1
12    8     2       区2
13    8     3       区3
14    8     4       区4总体效果图：id    pid   sort    name
-----------------------------------------------
1     0     1       中国
4     1     1       ----北京
5     1     2       ----广西
8     5     1       --------南宁
11    8     1       ------------区1
12    8     2       ------------区2
13    8     3       ------------区3
14    8     4       ------------区4
9     5     2       --------桂林
10    5     3       --------柳州
6     1     3       ----广州
7     1     4       ----上海
2     0     2       美国
3     0     3       英国需要的查询结果：
列表查询一：（输入：pid=0）id    pid   sort    name
-----------------------------------------------
1     0     1       中国
4     1     1       北京
5     1     2       广西
8     5     1       南宁
11    8     1       区1
12    8     2       区2
13    8     3       区3
14    8     4       区4
9     5     2       桂林
10    5     3       柳州
6     1     3       广州
7     1     4       上海
2     0     2       美国
3     0     3       英国列表查询二：（输入：pid=1）id    pid   sort    name
-----------------------------------------------
4     1     1       北京
5     1     2       广西
8     5     1       南宁
11    8     1       区1
12    8     2       区2
13    8     3       区3
14    8     4       区4
9     5     2       桂林
10    5     3       柳州
6     1     3       广州
7     1     4       上海列表查询三：（输入：pid=8）id    pid   sort    name
-----------------------------------------------
11    8     1       区1
12    8     2       区2
13    8     3       区3
14    8     4       区4

Answer 1

--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t 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','小分市'--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
SET @Level=@Level+1
INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
FROM @t a,@t_Level b
WHERE a.PID=b.ID
AND b.Level=@Level-1
END--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
  |--烟台市
    |--招远市
  |--青岛市
|--四会市
  |--清远市
    |--小分市
--*/

Answer 2

if exists (select * from dbo.sysobjects where id = object_id(N'[tb]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
    drop table [tb]
GO-- 示例数据
create table [tb]([id] int PRIMARY KEY,[pid] int,name nvarchar(20))
INSERT [tb] SELECT  1,0,N'中国'
UNION  ALL  SELECT  2,0,N'美国'
UNION  ALL  SELECT  3,0,N'加拿大'
UNION  ALL  SELECT  4,1,N'北京'
UNION  ALL  SELECT  5,1,N'上海'
UNION  ALL  SELECT  6,1,N'江苏'
UNION  ALL  SELECT  7,6,N'苏州'
UNION  ALL  SELECT  8,7,N'常熟'
UNION  ALL  SELECT  9,6,N'南京'
UNION  ALL  SELECT 10,6,N'无锡'
UNION  ALL  SELECT 11,2,N'纽约'
UNION  ALL  SELECT 12,2,N'旧金山'
GO -- 查询指定id的所有子 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_cid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_cid]
GO
-- =====================================================
-- 查询指定id的所有子
-- 邹建 2005-07(引用请保留此信息)-- 调用示例/*--调用(查询所有的子)
    SELECT A.*,层次=B.[level] 
    FROM [tb] A,f_cid(2)B
    WHERE A.[id]=B.[id]
--*/
-- =====================================================
CREATE FUNCTION f_cid(@id int)
RETURNS TABLE
AS
RETURN(
    WITH ctb([id],[level])
    AS(
        SELECT [id],1 FROM [tb]
        WHERE [pid]=@id
        UNION ALL
        SELECT A.[id],B.[level]+1
        FROM [tb] A,ctb B
        WHERE A.[pid]=B.[id])
    SELECT * FROM ctb
    --如果只显示最明细的子(下面没有子),则将上面这句改为下面的
--    SELECT * FROM ctb A
--    WHERE NOT EXISTS(
--        SELECT 1 FROM [tb] WHERE [pid]=A.[id])
)
GO--调用(查询所有的子)
SELECT A.*,层次=B.[level] 
FROM [tb] A,f_cid(2)B
WHERE A.[id]=B.[id]
GO -- 查询指定id的所有父 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_pid]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[f_pid]
GO
-- =====================================================
-- 查询指定id的所有父
-- 邹建 2005-07(引用请保留此信息)-- 调用示例/*--调用(查询所有的父)
    SELECT A.*,层次=B.[level] 
    FROM [tb] A,[f_pid](2)B
    WHERE A.[id]=B.[id]
--*/
-- =====================================================
CREATE FUNCTION [f_pid](@id int)
RETURNS TABLE
AS
RETURN(
    WITH ptb([id],[level])
    AS(
        SELECT [pid],1 FROM [tb]
        WHERE [id]=@id
            AND [pid]<>0
        UNION ALL
        SELECT A.[pid],B.[level]+1
        FROM [tb] A,ptb B
        WHERE A.[id]=B.[id]
            AND [pid]<>0)
    SELECT * FROM ptb
)
GO--调用(查询所有的父)
SELECT A.*,层次=B.[level] 
FROM [tb] A,[f_pid](7)B
WHERE A.[id]=B.[id]
GO -- 树形分级显示 
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_id]') and xtype in (N'FN', N'IF', N'TF'))
    drop function [dbo].[f_id]
GO
-- =====================================================
-- 级别及排序字段(树形分级显示)
-- 邹建 2005-07(引用请保留此信息)-- 调用示例/*--调用实现树形显示    --调用函数实现分级显示
    SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name
    FROM [tb] A,f_id()B 
    WHERE a.[id]=b.[id]
    ORDER BY b.sid    --当然,这个也可以根本不用写函数,直接排序即可
    WITH stb([id],[level],[sid])
    AS(
        SELECT [id],1,CAST(RIGHT(10000+[id],4) as varchar(8000))
        FROM [tb]
        WHERE [pid]=0
        UNION ALL
        SELECT A.[id],B.[level]+1,B.sid+RIGHT(10000+A.[id],4)
        FROM [tb] A,stb B
        WHERE A.[pid]=B.[id])
    SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name
    FROM [tb] A,stb B 
    WHERE a.[id]=b.[id]
    ORDER BY b.sid    
--*/
-- =====================================================
CREATE FUNCTION f_id()
RETURNS TABLE
AS
RETURN(
    WITH stb([id],[level],[sid])
    AS(
        SELECT [id],1,CAST(RIGHT(10000+[id],4) as varchar(8000))
        FROM [tb]
        WHERE [pid]=0
        UNION ALL
        SELECT A.[id],B.[level]+1,B.sid+RIGHT(10000+A.[id],4)
        FROM [tb] A,stb B
        WHERE A.[pid]=B.[id])
    SELECT * FROM stb
)
GO--调用函数实现分级显示
SELECT N'|'+REPLICATE('-',B.[level]*4)+A.name
FROM [tb] A,f_id()B 
WHERE a.[id]=b.[id]
ORDER BY b.sid
GO 

Answer 3

參照2000\05：
http://blog.csdn.net/roy_88/archive/2006/12/24/1458449.aspx
http://blog.csdn.net/roy_88/archive/2008/01/15/2045842.aspx

Answer 4

使用递归公用表表达式显示递归的多个级别USE AdventureWorks;
GO
WITH DirectReports(ManagerID, EmployeeID, EmployeeLevel) AS 
(
    SELECT ManagerID, EmployeeID, 0 AS EmployeeLevel
    FROM HumanResources.Employee
    WHERE ManagerID IS NULL
    UNION ALL
    SELECT e.ManagerID, e.EmployeeID, EmployeeLevel + 1
    FROM HumanResources.Employee e
        INNER JOIN DirectReports d
        ON e.ManagerID = d.EmployeeID 
)
SELECT ManagerID, EmployeeID, EmployeeLevel 
FROM DirectReports ;
GOSQL2005--

Answer 5

--> liangCK小梁 于2008-10-09
--> 生成测试数据: #T
IF OBJECT_ID('tempdb.dbo.#T') IS NOT NULL DROP TABLE #T
CREATE TABLE #T (id INT,pid INT,sort INT,name NVARCHAR(4))
INSERT INTO #T
SELECT '1','0','1','中国' UNION ALL
SELECT '2','0','2','美国' UNION ALL
SELECT '3','0','3','英国' UNION ALL
SELECT '4','1','1','北京' UNION ALL
SELECT '5','1','2','广西' UNION ALL
SELECT '6','1','3','广州' UNION ALL
SELECT '7','1','4','上海' UNION ALL
SELECT '8','5','1','南宁' UNION ALL
SELECT '9','5','2','桂林' UNION ALL
SELECT '10','5','3','柳州' UNION ALL
SELECT '11','8','1','区1' UNION ALL
SELECT '12','8','2','区2' UNION ALL
SELECT '13','8','3','区3' UNION ALL
SELECT '14','8','4','区4'--SQL查询如下:;WITH CTE AS
(
  SELECT id,name,pid,
         path=CAST(name AS NVARCHAR(MAX)),level=1
  FROM #T 
  WHERE pid=0
  
  UNION ALL
  
  SELECT t.id,t.name,t.pid,
         CAST(c.path+t.name AS NVARCHAR(MAX)),c.level+1
  FROM CTE AS c
    JOIN #T AS t
   ON c.id=t.pid
)
SELECT id,pid,REPLICATE('--',level)+name AS name
FROM CTE
ORDER BY path/*
id          pid         name
----------- ----------- ---------------
1           0           --中国
7           1           ----上海
4           1           ----北京
6           1           ----广州
5           1           ----广西
8           5           ------南宁
11          8           --------区1
12          8           --------区2
13          8           --------区3
14          8           --------区4
10          5           ------柳州
9           5           ------桂林
2           0           --美国
3           0           --英国(14 行受影响)
*/

Answer 6

use tempdb
go
--> --> 
 
if not object_id('T') is null
drop table T
Go
Create table T([id] int,[pid] int,[sort] int,[name] nvarchar(2))
Insert T
select 1,0,1,N'中国' union all
select 2,0,2,N'美国' union all
select 3,0,3,N'英国' union all
select 4,1,1,N'北京' union all
select 5,1,2,N'广西' union all
select 6,1,3,N'广州' union all
select 7,1,4,N'上海' union all
select 8,5,1,N'南宁' union all
select 9,5,2,N'桂林' union all
select 10,5,3,N'柳州' union all
select 11,8,1,N'区1' union all
select 12,8,2,N'区2' union all
select 13,8,3,N'区3' union all
select 14,8,4,N'区4'
Go
create function F_BOM(@PID int)
returns @T table([id] int,[pid] int,[sort] int,[name] nvarchar(2))
as
begin 
;with C as
(select *,ord=cast(right(1000+[sort],3) as nvarchar(2000)) from T where [pid]=@PID
union all
select t.*,cast(c.ord+right(1000+t.[sort],3) as nvarchar(2000)) from C join T on t.[pid]=c.ID)
insert @T select [id],[pid],[sort],[name] from C order by ord
return
end
go
--測試
select * from F_BOM(0) 
select * from F_BOM(1)

Answer 7

SQLServer 没有Oracle的( connect by prior id = pid start with ... )好用说明：
1、CET好像不支持变量输入，只能查询全部的列表，不能部分查询。
2、问题中要求：同级按sort排序（sort的我定义的，不是SQLServer分配的）。
3、roy_88给出的函数符合我的要求，支持动态递归查询，^^谢谢了。感谢各位的回复！

Answer 8

create function F_FUN(@MID int, @PID int, @FLAG int)
returns @FunList table([ID] int, [MENU_ID] int, [PARENT_ID] int, [NAME] varchar(50), [SORT] int, [URL] varchar(100), [ISUSED] varchar(10), [DEL_FLAG] varchar(1))
as
begin 
    if (@FLAG=1)
begin
with FunCET as 
(
select *, ord = cast(right(1000 + [sort], 3) as nvarchar(2000)) from T_FUN where [MENU_ID] = @MID and [PARENT_ID] = @PID and [ISUSED]=1
union all
select T_FUN.*, cast(FunCET.ord + right(1000 + T_FUN.[sort], 3) as nvarchar(2000)) from FunCET join T_FUN on T_FUN.[PARENT_ID] = FunCET.ID where FunCET.[ISUSED]=1
)
insert @FunList select [ID], [MENU_ID], [PARENT_ID], [NAME], [SORT], [URL], [ISUSED], [DEL_FLAG] from FunCET order by ord
end
else
begin
with FunCET as 
(
select *, ord = cast(right(1000 + [sort], 3) as nvarchar(2000)) from T_FUN where [MENU_ID] = @MID and [PARENT_ID] = @PID
union all
select T_FUN.*, cast(FunCET.ord + right(1000 + T_FUN.[sort], 3) as nvarchar(2000)) from FunCET join T_FUN on T_FUN.[PARENT_ID] = FunCET.ID
)
insert @FunList select [ID], [MENU_ID], [PARENT_ID], [NAME], [SORT], [URL], [ISUSED], [DEL_FLAG] from FunCET order by ord
end
return
end