問題描述
我已經(jīng)檢查了這個(gè)問題，但這有所不同，因?yàn)槲业奈锲房赡軙?huì)掉落在多個(gè)父節(jié)點(diǎn)而不是單個(gè)節(jié)點(diǎn)下，我有一個(gè)額外的映射表，而不是一個(gè)表中的所有內(nèi)容.
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.
我有一個(gè)將產(chǎn)品映射到類別的層次結(jié)構(gòu)，類別有 3 層深(深度在 articlegroups.catlevel 中定義，0 是主要類別，向下遍歷到較低的類別級(jí)別 2).此外，一個(gè)產(chǎn)品可能屬于 1 個(gè)以上的類別(！).
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(!).
產(chǎn)品詳細(xì)信息存儲(chǔ)在 [products]
文章組在 [articlegroups]
中定義產(chǎn)品到商品組的映射在[products_category_mapping]
現(xiàn)在，我想檢索每個(gè)項(xiàng)目的完整類別路徑的索引，因此使用下面提供的數(shù)據(jù)，我希望結(jié)果是這兩行:
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
現(xiàn)在我可以通過這樣的語(yǔ)句獲取單獨(dú)的字段:
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
但這只是給了我這個(gè)結(jié)果:
But that just gives me this result:
taarttoppers
grappig
bruidstaart
taarttoppers
grappig
但是，我不知道如何根據(jù)該類別級(jí)別的深度連接不同的類別級(jí)別，并在兩者之間使用 '>' 字符.
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.
表格+數(shù)據(jù)腳本
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
推薦答案
存儲(chǔ)模型的整個(gè)層次結(jié)構(gòu)的數(shù)據(jù)模型有助于您在獲取組時(shí)不必使用遞歸，而是能夠要將其用于路徑，您還需要為每一行存儲(chǔ)頂級(jí)文章組，以便它可用于對(duì)數(shù)據(jù)進(jìn)行分組.我對(duì) articlegroups 表進(jìn)行了更改，使其包含 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
這樣你就可以簡(jiǎn)單地獲取這樣的名字:
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 中的示例.
當(dāng)然，這種設(shè)計(jì)的缺點(diǎn)是，如果層次結(jié)構(gòu)發(fā)生變化，則必須將它們更新到每個(gè)產(chǎn)品.另一種選擇是將項(xiàng)目存儲(chǔ)到最低級(jí)別并使用遞歸 CTE 來獲取層次結(jié)構(gòu).這是一個(gè)維護(hù)起來更簡(jiǎn)單的模型，但讀取速度沒有那么快，因?yàn)槊看味夹枰幚磉f歸.
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.
這篇關(guān)于將層次結(jié)構(gòu)中的類別連接成單個(gè)字符串的 SQL 語(yǔ)句的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！