树型结构,计算父ID和深度

树形结构，计算父ID和深度

原有表结构
key
--------------------------------------------
country.china
country.china.beijing
country.china.beijing.haidian
country.china.beijing.haidian.xiangsan
country.china.beijing.chaoyang
country.china.beijing.chaoyang.cbd
country.china.dalian
country.china.shanghai

现在有修改成这样
id      pid      depth    key                                 
-----    ------    -----    -----------------------------------
1        0        1        country.china
2        1        2        country.china.beijing
3        2        3        country.china.beijing.haidian
4        3        4        country.china.beijing.haidian.xiangsan
5        2        3        country.china.beijing.chaoyang
6        5        4        country.china.beijing.chaoyang.cbd
7        1        2        country.china.dalian
8        1        2        country.china.shanghai

id是自动增长，表格太长手工修改pid有点…… 不知道有和办法计算并更新pid和depth

 



/*
id                   pid                  depth       key
-------------------- -------------------- ----------- --------------------------------------
1                    0                    1           country.china
2                    1                    2           country.china.beijing
3                    2                    3           country.china.beijing.haidian
4                    3                    4           country.china.beijing.haidian.xiangsan
5                    2                    3           country.china.beijing.chaoyang
6                    5                    4           country.china.beijing.chaoyang.cbd
7                    1                    2           country.china.dalian
8                    1                    2           country.china.shanghai

(8 行受影响)
*/

--> 生成测试数据: @T

DECLARE @T TABLE ([key] VARCHAR(38))

INSERT INTO @T

SELECT 'country.china' UNION ALL

SELECT 'country.china.beijing' UNION ALL

SELECT 'country.china.beijing.haidian' UNION ALL

SELECT 'country.china.beijing.haidian.xiangsan' UNION ALL

SELECT 'country.china.beijing.chaoyang' UNION ALL

SELECT 'country.china.beijing.chaoyang.cbd' UNION ALL

SELECT 'country.china.dalian' UNION ALL

SELECT 'country.china.shanghai'

 

--SQL查询如下:

 

;WITH t AS

(

    SELECT [key],id=ROW_NUMBER() OVER(ORDER BY GETDATE())

    FROM @T

)

SELECT id,

       ISNULL(

              (

                 SELECT MAX(id)

                 FROM t

                 WHERE a.[key] LIKE [key]+'.%'

              ), 0

       ) AS pid,

       LEN([key])-LEN(REPLACE([key], '.', '')) AS depth,

       [key]

FROM t AS a