如何以递归方式获取逗号分隔的 CategoryIds? [英] How to get comma delimited CategoryIds recursively?

问题描述

我有一张这样的桌子:

Name           CategoryId    ParentCategoryId
Footwear       93            0
Men Shoes      6             93
Female Shoes   7             93
Mobile         2             0
Smartphone     4             2

我需要这样的输出:

Name            Categories 
Footwear        93,0    
Men Shoes       6,93,0    
Female Shoes    7,93,0    
Mobile          2,0   
Smartphone      4,2,0       

基本上，我需要递归获取类别 id 并将它们变成逗号分隔的字符串.3 年后我开始使用 SQL，但我不知道如何得到这个结果.我已经尝试过其他 SO 问题的解决方案，但仍然没有运气.

Basically, I need to recursively get the category ids and make them into a comma delimited string. I am getting into SQL after 3 years now and I have no idea how to get this result. I have tried solutions from other SO questions but still no luck.

推荐答案

你用递归 cte 做到这一点:

You do this with recursive cte:

DECLARE @t TABLE
    (
      Name VARCHAR(100) ,
      CategoryId INT ,
      ParentCategoryId INT
    )
INSERT  INTO @t
VALUES  ( 'Footwear', 93, 0 ),
        ( 'Men Shoes', 6, 93 ),
        ( 'Female Shoes', 7, 93 ),
        ( 'Mobile', 2, 0 ),
        ( 'Smartphone', 4, 2 );

WITH    cte
          AS ( SELECT   * ,
                        CAST(CategoryId AS VARCHAR(100))  AS Categories
               FROM     @t
               WHERE    ParentCategoryId = 0
               UNION ALL
               SELECT   t.* ,
                        CAST(CAST(t.CategoryId AS VARCHAR(100)) + ','
                        + c.Categories AS VARCHAR(100))
               FROM     @t t
                        JOIN cte c ON c.CategoryId = t.ParentCategoryId
             )
    SELECT  *
    FROM    cte

这篇关于如何以递归方式获取逗号分隔的 CategoryIds?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！