求SQL高手,怎么解决祖孙的层次关系呢?

Question

有一个表,显示了父子直接的关系
ID    Name   ParentID
1     全国
2     华北     1
3     东北     1
4     华中     1
5     山东     2
6     河北     2
7     辽宁     3
8     济南     5
9     青岛     5如何根据这个表得到所有的祖孙结构问题?层次数表示祖孙几代,谢谢了ParentID   ParentName  ChildID  ChildName    层次数
1            全国       2         华北          1
1            全国       3         东北          1
1            全国       4         华中          1
1            全国       5         山东          2
1            全国       6         河北          2
1            全国       7         辽宁          2
1            全国       8         济南          3
1            全国       9         青岛          3
2            华北       5         山东          1
2            华北       6         河北          1
2            华北       8         济南          2
2            华北       9         青岛          2
3            东北       7         辽宁          1
5            山东       8         济南          1
5            山东       9         青岛          1

Answer 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_Sort(@ID char(3)=NULL,@sort int=1)
RETURNS @t_Level TABLE(ID char(3),sort int)
AS
BEGIN
DECLARE tb CURSOR LOCAL
FOR
SELECT ID FROM tb
WHERE PID=@ID
OR(@ID IS NULL AND PID IS NULL)
OPEN TB
FETCH tb INTO @ID
WHILE @@FETCH_STATUS=0
BEGIN
INSERT @t_Level VALUES(@ID,@sort)
SET @sort=@sort+1
IF @@NESTLEVEL<32 --如果递归层数未超过32层(递归最大允许32层)
BEGIN
--递归查找当前节点的子节点
INSERT @t_Level SELECT * FROM f_Sort(@ID,@sort)
SET @sort=@sort+@@ROWCOUNT  --排序号加上子节点个数
END
FETCH tb INTO @ID
END
RETURN
END
GO--显示结果
SELECT a.*
FROM tb a,f_Sort(DEFAULT,DEFAULT) b
WHERE a.ID=b.ID
ORDER BY b.sort
/*--结果
ID   PID   Name       
------ --------- ---------- 
001  NULL 山东省
002  001   烟台市
004  002   招远市
003  001   青岛市
005  NULL 四会市
006  005   清远市
007  006   小分市
--*/

Answer 2

DECLARE @tb TABLE([ID] int, [Name] nvarchar(10), [ParentID] int)
INSERT INTO @tb
    SELECT  1, N'全国', NULL
    UNION ALL SELECT 2, N'华北', 1
    UNION ALL SELECT 3, N'东北', 1
    UNION ALL SELECT 4, N'华中', 1
    UNION ALL SELECT 5, N'山东', 2
    UNION ALL SELECT 6, N'河北', 2
    UNION ALL SELECT 7, N'辽宁', 3
    UNION ALL SELECT 8, N'济南', 5
    UNION ALL SELECT 9, N'青岛', 5DECLARE @t TABLE([ParentID] int, [ParentName] nvarchar(10), [ChildID] int, [ChildName] NVARCHAR(10), [DEP] INT)INSERT INTO @T
SELECT B.[ID] AS ParentID, B.[NAME] AS ParentName, A.[ID] AS ChildID, A.[NAME] AS ChildName, 1 AS DEP
FROM @TB A INNER JOIN @TB B ON A.ParentID = B.[ID]WHILE @@ROWCOUNT > 0
BEGIN
    INSERT INTO @T
    SELECT T1.ParentID, T1.ParentName, T1.ChildID, T1.ChildName, T1.DEP FROM
    (SELECT B.ParentID, B.ParentName, A.[ID] AS ChildID, A.[NAME] AS ChildName, B.DEP + 1 AS DEP
    FROM @TB A INNER JOIN @T B ON A.[ParentID] = B.[ChildID])T1
    LEFT JOIN @T T2 ON T1.ParentID = T2.ParentID AND T1.ChildID = T2.ChildID
    WHERE T2.ParentID IS NULL
ENDSELECT * FROM @T ORDER BY ParentID, DEP