久草资源在线观看,天堂成人网,在线观看免费黄色

本文介紹了將層次結構中的類別連接成單個字符串的 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.

這篇關于將層次結構中的類別連接成單個字符串的 SQL 語句的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！

【網站聲明】本站部分內容來源于互聯網,旨在幫助大家更快的解決問題，如果有圖片或者內容侵犯了您的權益，請聯系我們刪除處理，感謝您的支持！

pbootcms网站模板|日韩1区2区|织梦模板||网站源码|日韩1区2区|jquery建站特效-html5模板网

將層次結構中的類別連接成單個字符串的 SQL 語句

問題描述

推薦答案

相關文檔推薦