将层次结构中的类别连接成单个字符串的 SQL 语句

问题描述

我已经检查了这个问题，但这有所不同，因为我的物品可能会掉落在多个父节点而不是单个节点下，我有一个额外的映射表，而不是一个表中的所有内容.

I already checked this question, but that is different as my items can fall under multiple parent nodes and not a single one and I have an extra mapping table instead of everything in one table.

我有一个将产品映射到类别的层次结构，类别有 3 层深(深度在 articlegroups.catlevel 中定义，0 是主要类别，向下遍历到较低的类别级别 2).此外，一个产品可能属于 1 个以上的类别(！).

I have a hierarchical structure for mapping products to categories, categories go 3 levels deep (depth is defined in articlegroups.catlevel, 0 being the main category and traversing down to lower category level 2). Also, a product may be in more than 1 category(!).

产品详细信息存储在 [products]
文章组在 [articlegroups]
中定义产品到商品组的映射在[products_category_mapping]

现在，我想检索每个项目的完整类别路径的索引，因此使用下面提供的数据，我希望结果是这两行:

Now, I want to retrieve index the full category path for each item, so with the data provided below, I'd expect these 2 rows as a result:

id          categorystring
2481446     Taarttoppers > Taarttoppers grap'pig  
2481446     Bruidstaart > Taarttoppers > Grappig

现在我可以通过这样的语句获取单独的字段:

Now I can get the separate fields via a statement like this:

SELECT ga.slug_nl as slug_nl_0
FROM articlegroups ga
INNER JOIN products_category_mapping pcm ON pcm.articlegroup_id=ga.id
INNER JOIN products gp on gp.id=pcm.artikelid
WHERE gp.id=2481446

但这只是给了我这个结果:

But that just gives me this result:

taarttoppers
grappig
bruidstaart
taarttoppers
grappig

但是，我不知道如何根据该类别级别的深度连接不同的类别级别，并在两者之间使用 '>' 字符.

However, I don't know how to concatenate the different category levels respecting the depth of that category level and have a '>' character in between.

表格+数据脚本

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[articlegroups](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [parentid] [int] NOT NULL,
    [catlevel] [tinyint] NOT NULL CONSTRAINT [DF_articlegroups_lvl0_catlevel]  DEFAULT ((0)),
    [slug_nl] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_articlegroups] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[products]    Script Date: 28-07-15 15:45:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[products](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [artikelnummer] [nvarchar](60) NOT NULL
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[products_category_mapping]    Script Date: 28-07-15 15:45:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[products_category_mapping](
    [artikelid] [int] NOT NULL,
    [articlegroup_id] [int] NOT NULL,
    [createdate] [datetime] NOT NULL CONSTRAINT [DF_products_category_mapping_createdate]  DEFAULT (getdate())
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[articlegroups] ON 

GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (1, 0, 0, N'taarttoppers')
GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (2, 1, 1, N'grappig')
GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (3, 0, 0, N'feestartikelen')
GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (4, 3, 1, N'ballonnen')
GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (5, 3, 1, N'slingers')
GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (6, 0, 0, N'bruidstaart')
GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (7, 6, 1, N'taarttoppers')
GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (8, 7, 2, N'grappig')
GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (9, 0, 0, N'accessoires')
GO
INSERT [dbo].[articlegroups] ([id], [parentid], [catlevel], [slug_nl]) VALUES (10, 9, 1, N'tiaras')
GO
SET IDENTITY_INSERT [dbo].[articlegroups] OFF
GO
SET IDENTITY_INSERT [dbo].[products] ON 

GO
INSERT [dbo].[products] ([id], [artikelnummer]) VALUES (2481446, N'1013')
GO
SET IDENTITY_INSERT [dbo].[products] OFF
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481446, 1, CAST(N'2015-07-24 20:27:02.890' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481446, 2, CAST(N'2015-07-24 20:27:02.890' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481446, 6, CAST(N'2015-07-24 20:27:02.890' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481446, 7, CAST(N'2015-07-24 20:27:02.890' AS DateTime))
GO
INSERT [dbo].[products_category_mapping] ([artikelid], [articlegroup_id], [createdate]) VALUES (2481446, 8, CAST(N'2015-07-24 20:27:02.890' AS DateTime))
GO
/****** Object:  Index [PK_products]    Script Date: 28-07-15 15:45:03 ******/
ALTER TABLE [dbo].[products] ADD  CONSTRAINT [PK_products] PRIMARY KEY NONCLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE [dbo].[products_category_mapping]  WITH CHECK ADD  CONSTRAINT [FK_articlegroups_lvl1_mapping_products] FOREIGN KEY([artikelid])
REFERENCES [dbo].[products] ([id])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[products_category_mapping] CHECK CONSTRAINT [FK_articlegroups_lvl1_mapping_products]
GO

推荐答案

存储模型的整个层次结构的数据模型有助于您在获取组时不必使用递归，而是能够要将其用于路径，您还需要为每一行存储顶级文章组，以便它可用于对数据进行分组.我对 articlegroups 表进行了更改，使其包含 toplevelid:

The data model you have where you store the whole hierarchy for the model helps so that you don't have to use recursion when fetching the groups, but being able to use it for the path, you would need to have also the top level article group to be stored for each row so that it can be used for grouping the data. I made a change to the articlegroups table so that it it contains the toplevelid:

id  parentid    catlevel   toplevelid   slug_nl
1   0           0          1            taarttoppers
2   1           1          1            grappig
3   0           0          3            feestartikelen
4   3           1          3            ballonnen
5   3           1          3            slingers
6   0           0          6            bruidstaart
7   6           1          6            taarttoppers
8   7           2          6            grappig
9   0           0          9            accessoires
10  9           1          9            tiaras

这样你就可以简单地获取这样的名字:

This way you can simply fetch the names like this:

SELECT tmp.toplevelid, categorystring = STUFF((SELECT N' > ' + slug_nl 
  FROM articlegroups AS ga2
   WHERE ga2.toplevelid = tmp.toplevelid 
   ORDER BY catlevel
   FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 3, '')
FROM 
  products gp
  INNER JOIN products_category_mapping pcm ON gp.id=pcm.artikelid
  outer apply (
    select distinct ga.toplevelid
    from articlegroups ga
    where  pcm.articlegroup_id=ga.id
  ) tmp 
WHERE gp.id=2481446
GROUP BY tmp.toplevelid
ORDER BY tmp.toplevelid;

SQL Fiddle 中的示例.

当然，这种设计的缺点是，如果层次结构发生变化，则必须将它们更新到每个产品.另一种选择是将项目存储到最低级别并使用递归 CTE 来获取层次结构.这是一个维护起来更简单的模型，但读取速度没有那么快，因为每次都需要处理递归.

The downside of this design of course is that if you have changes in the hierarchy, you'll have to update them to every product. The other option is to store the items just to the lowest level and use a recursive CTE to fetch the hierarchy. That's a simpler model to maintain, but it's not as fast to read because the recursion needs to be handled every time.