上下级关系的处理,实例

[marongc]

表T,有A和B两个字段

如：
A B
---------------------------------
111 [空]
222 111
333 222
444 111
555 [空]

我想要的结果
V1 V2
-------------------------------------
111 111
222 111/222
333 111/222/333
444 111/444
555 555

[marongc]

字段B指的是字段A的上级,最多只有三级,请大家帮忙 ,先谢谢大家了.

[marongc]

with T AS(
SELECT '111'AS A, ''AS B UNION ALL
SELECT '222'AS A, '111'AS B UNION ALL
SELECT '333'AS A, '222'AS B UNION ALL
SELECT '444'AS A, '111'AS B UNION ALL
SELECT '555'AS A, ''AS B 
)
SELECT * FROM T

[Pact_Alice]

;with T AS( SELECT '111'AS A, ''AS B UNION ALL SELECT '222'AS A, '111'AS B UNION ALL SELECT '333'AS A, '222'AS B UNION ALL SELECT '444'AS A, '111'AS B UNION ALL SELECT '555'AS A, ''AS B ),cet AS ( SELECT A,B, CAST(1 AS INT)AS level FROM T WHERE T.B='' UNION ALL SELECT t1.A,t1.B,t2.level+1 FROM T t1,cet t2 WHERE t1.B=t2.A ) SELECT A, *, CASE WHEN LEVEL=1 THEN A WHEN LEVEL=2 THEN B+'/'+A WHEN LEVEL=3 THEN '111'+B+'/'+A END FROM cet

[Tiger_Zhao]

with T AS(
SELECT '111'AS A, ''AS B UNION ALL
SELECT '222'AS A, '111'AS B UNION ALL
SELECT '333'AS A, '222'AS B UNION ALL
SELECT '444'AS A, '111'AS B UNION ALL
SELECT '555'AS A, ''AS B 
)
,tree AS(
    SELECT *,
           CONVERT(varchar(30),A) AS V2
      FROM t
     WHERE B=''
    UNION ALL
    SELECT t.*,
           CONVERT(varchar(30),tree.V2+'\'+t.A) AS V2
      FROM tree
      JOIN t
        ON tree.A = t.B
)
    SELECT A AS V1,
           V2
      FROM tree
  ORDER BY V2

V1   V2
---- ------------------------------
111  111
222  111\222
333  111\222\333
444  111\444
555  555

[marongc]

引用 4 楼 Tiger_Zhao 的回复:

with T AS(
SELECT '111'AS A, ''AS B UNION ALL
SELECT '222'AS A, '111'AS B UNION ALL
SELECT '333'AS A, '222'AS B UNION ALL
SELECT '444'AS A, '111'AS B UNION ALL
SELECT '555'AS A, ''AS B 
)
,tree AS(
    SELECT *,
           CONVERT(varchar(30),A) AS V2
      FROM t
     WHERE B=''
    UNION ALL
    SELECT t.*,
           CONVERT(varchar(30),tree.V2+'\'+t.A) AS V2
      FROM tree
      JOIN t
        ON tree.A = t.B
)
    SELECT A AS V1,
           V2
      FROM tree
  ORDER BY V2

V1   V2
---- ------------------------------
111  111
222  111\222
333  111\222\333
444  111\444
555  555

数据有1万条的时候,好慢