SQL查询子节点或父节点

查询指定节点及其所有子节点或父节点

 

****************************查询开始******************************
--SQL2005 使用CTE
DECLARE @ VARCHAR(10)
SET @='栾川县'
;WITH XiaoAi AS
(
SELECT id FROM tb WHERE [name]=@
UNION ALL
SELECT t.id FROM XiaoAi AS a INNER JOIN tb AS t ON a.id=t.pid
)
SELECT t.* FROM XiaoAi AS a LEFT JOIN tb AS t ON t.id=a.id

--SQL 2000 使用函数
IF OBJECT_ID('dbo.XiaoAi') IS NOT NULL DROP FUNCTION dbo.XiaoAi
GO
CREATE FUNCTION dbo.XiaoAi(@ VARCHAR(20))
RETURNS @t TABLE(id VARCHAR(3), pid VARCHAR(3), [name] VARCHAR(20),Level INT)
AS
BEGIN
    DECLARE @level INT
    SET @level=1
    INSERT INTO @t SELECT *,@level FROM tb WHERE [name]=@
    WHILE(@@ROWCOUNT>0)
        BEGIN
            SET @level=@level+1
            INSERT INTO @t SELECT t.*,@level FROM tb AS t,@t AS a WHERE a.id=t.pid AND a.level=@level-1
        END
RETURN
END
GO
--调用函数
SELECT id, pid,[name]  from dbo.XiaoAi('栾川县')
--****************************查询结束*************************************
/*
2、查询指定节点及其所有父节点
如：已知 栾川县
得到以下结果
id   pid  name
---- ---- ----------
001  NULL 河南省
002  001  洛阳市
004  002  栾川县
*/
----------------------测试开始-------------------------------------------------
DECLARE @s VARCHAR(10)
SET @s='栾川县'
SELECT id, pid,[name] INTO # FROM tb WHERE [name]=@s
WHILE @@ROWCOUNT>0
    BEGIN
    INSERT INTO # SELECT t.id, t.pid,t.[name] FROM tb AS t
                                INNER JOIN # AS a ON t.id=a.pid AND t.id NOT IN(SELECT ID FROM #)
    END
SELECT * FROM # ORDER BY ID
-----------------------测试结束-------------------------------------------------

IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
GO
CREATE TABLE tb(id VARCHAR(3),pid VARCHAR(3),[name] VARCHAR(10))
GO
INSERT INTO tb SELECT '001',NULL,'河南省'
UNION ALL SELECT '002','001','洛阳市'
UNION ALL SELECT '003','001','新乡市'
UNION ALL SELECT '004','002','栾川县'
UNION ALL SELECT '005','003','长垣县'
UNION ALL SELECT '006','002','孟津县'
UNION ALL SELECT '007','004','冷水乡'
UNION ALL SELECT '008','004','叫河乡'
UNION ALL SELECT '009','008','A村'
UNION ALL SELECT '010','008','B村'
GO
/*
1、查询指定节点及其所有子节点
--如：已知 栾川县
--得到结果

id   pid  name
---- ---- ----------
004  002  栾川县
007  004  冷水乡
008  004  叫河乡
009  008  A村
010  008  B村
*/