查询指定节点及其父节点

CREATE TABLE TCity
(
    ID VARCHAR(10), 
    ParentId VARCHAR(10), 
    Name VARCHAR(10)
)
GO

INSERT INTO TCity VALUES('001', NULL, '广东省')
INSERT INTO TCity VALUES('002', '001', '东莞市')
INSERT INTO TCity VALUES('003', '001', '深圳市')

INSERT INTO TCity VALUES('004', '002', '南城')

INSERT INTO TCity VALUES('005', '003', '罗湖区')
INSERT INTO TCity VALUES('006', '003', '福田区')
INSERT INTO TCity VALUES('007', '003', '宝安区')

INSERT INTO TCity VALUES('008', '007', '西乡镇')
INSERT INTO TCity VALUES('009', '007', '龙华镇')

GO

 

SELECT * FROM TCity

 

DECLARE @Id VARCHAR(10) 
SET @Id='008'

--递归
;WITH cte AS 
(
    SELECT ID,ParentId,Name FROM TCity
    WHERE ID=@Id
    UNION ALL
    SELECT a.ID,a.ParentId,a.Name FROM TCity a
    INNER JOIN cte b ON b.ParentId=a.ID
)

SELECT * FROM cte 
ORDER BY ID

 

--查询各节点的父路径函数(从父到子)
CREATE FUNCTION fun_ParentToChildPath
(
    --参数
    @id VARCHAR(10)
)
--返回值
RETURNS VARCHAR(100)
AS
BEGIN
    DECLARE @result AS VARCHAR(100)
    SET @result=''

    SELECT @result = Name FROM dbo.TCity WHERE ID=@id
    WHILE EXISTS(SELECT 1 FROM dbo.TCity WHERE ID=@id AND ParentId IS NOT NULL)
    BEGIN
        SELECT    @id=b.ID, 
                @result = b.Name + ',' + @result    --广东省,深圳市,宝安区
                --@result = @result + ',' + b.Name  --宝安区,深圳市,广东省
        FROM dbo.TCity a,dbo.TCity b
        WHERE a.ID=@id AND a.ParentId=b.ID
    END

    RETURN @result
END

GO 

--查询各节点的父路径函数(从子到父)
CREATE FUNCTION fun_ChildToParentPath
(
    --参数
    @id VARCHAR(10)
)
--返回值
RETURNS VARCHAR(100)
AS
BEGIN
    DECLARE @result AS VARCHAR(100)
    SET @result=''

    SELECT @result = Name FROM dbo.TCity WHERE ID=@id
    WHILE EXISTS(SELECT 1 FROM dbo.TCity WHERE ID=@id AND ParentId IS NOT NULL)
    BEGIN
        SELECT    @id=b.ID, 
                --@result = b.Name + ',' + @result  --广东省,深圳市,宝安区 
                @result = @result + ',' + b.Name    --宝安区,深圳市,广东省
        FROM dbo.TCity a,dbo.TCity b
        WHERE a.ID=@id AND a.ParentId=b.ID
    END

    RETURN @result
END

GO


DECLARE @Id VARCHAR(10) 
SET @Id='008'

SELECT *
    ,dbo.fun_ParentToChildPath(ID) AS 'ParentToChildPath'
    ,dbo.fun_ChildToParentPath(ID) AS 'ChildToParentPath'
FROM dbo.TCity
--WHERE ID=@Id
ORDER BY ID

 

DECLARE @str VARCHAR(100)
SET @str = '0102030405060708091000232'

--计算字符串里面有多少个零(0)
SELECT  LEN(@str),
        LEN(REPLACE(@str,'0','')),
        LEN(@str)-LEN(REPLACE(@str,'0',''))

 

25 - 13 = 12