SQL Server CTE结果的逆序？

dgsult0t 于 2023-01-29 发布在其他

关注(0)|答案(1)|浏览(153)

下面是我的代码，从一个节点开始遍历所有节点：

SELECT * INTO MyTable
FROM
(
    SELECT 1 Id, 1 ParentId, 'Parent A' Name
    UNION ALL
    SELECT 5,1,'Child A1'
    UNION ALL
    SELECT 47894,5,'Child A2'
    UNION ALL
    SELECT 2,2, 'Parent B'
    UNION ALL
    SELECT 3,2, 'Child B1'
)TAB

;With CTE as
(
  select * from MyTable where Id = 47894
  union all
  select a.* from MyTable a inner join cte b 
    on a.Id=b.ParentId and a.Id<>b.Id
)

select STRING_AGG(Name, ' >> ') from CTE

输入47894时，它给出：

Child A2 >> Child A1 >> Parent A

如果我需要反向呢？

Parent A >> Child A1 >> Child A2

sql-server

来源：https://stackoverflow.com/questions/75244078/reverse-order-of-a-cte-result

1条答案

按热度按时间

fhg3lkii1#

您可以使用以下命令：

SELECT *
INTO   MyTable
FROM   (
          SELECT
             1 Id,
             1 ParentId,
             'Parent A' Name
          UNION ALL
          SELECT
             5,
             1,
             'Child A1'
          UNION ALL
          SELECT
             47894,
             5,
             'Child A2'
          UNION ALL
          SELECT
             2,
             2,
             'Parent B'
          UNION ALL
          SELECT
             3,
             2,
             'Child B1'
       ) TAB;
WITH CTE
AS (SELECT
          Id,
          ParentId,
          Name,
          CAST(1 AS INT) AS OrderByValue
    FROM  MyTable
    WHERE Id = 47894
    UNION ALL
    SELECT
         a.Id,
         a.ParentId,
         a.Name,
         OrderByValue + 1
    FROM MyTable a
         INNER JOIN CTE b ON a.Id = b.ParentId
                             AND a.Id <> b.Id)
SELECT STRING_AGG(Name, ' >> ')WITHIN GROUP(ORDER BY CTE.OrderByValue DESC)
FROM   CTE;

赞(0）回复(0）举报 2023-01-29

我来回答

SQL Server CTE结果的逆序？

1条答案

相关问题

热门标签

最新问答