大神们，帮小弟看下如何根据前面三个字段，用sql语句拼出最后一个字段的格式

[sunylang]

大神们，帮小弟看下如何根据前面三个字段，用sql语句拼出最后一个字段的格式

[Tiger_Zhao]

递归生成路径，只要前两个字段。

WITH
/* 测试数据
table1(AR_ID,PID)AS(
    SELECT 1,0 UNION ALL
    SELECT 2,1 UNION ALL
    SELECT 3,1 UNION ALL
    SELECT 19,1 UNION ALL
    SELECT 20,19 UNION ALL
    SELECT 21,19 UNION ALL
    SELECT 14,1 UNION ALL
    SELECT 15,2 UNION ALL
    SELECT 16,1 UNION ALL
    SELECT 17,2 UNION ALL
    SELECT 18,16
),*/
a AS (
    SELECT *,
           CONVERT(varchar(20),PID) AS SORT
      FROM table1
     WHERE PID = 0
    UNION ALL
    SELECT c.*,
           CONVERT(varchar(20),p.SORT+'_'+CONVERT(varchar(11),c.AR_ID)) AS result
      FROM a p
      JOIN table1 c
        ON p.AR_ID = c.PID
)
    SELECT *
      FROM a
  ORDER BY SORT

      AR_ID         PID SORT
----------- ----------- --------------------
          1           0 0
         14           1 0_14
         16           1 0_16
         18          16 0_16_18
         19           1 0_19
         20          19 0_19_20
         21          19 0_19_21
          2           1 0_2
         15           2 0_2_15
         17           2 0_2_17
          3           1 0_3

[阳泉酒家小当家]

楼上的代码就可以实现sql server里的递归的查询。