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村
*/