久久久久久久av_日韩在线中文_看一级毛片视频_日本精品二区_成人深夜福利视频_武道仙尊动漫在线观看

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

數據庫問題 html5模板網
SQL statement to concatenate categories from hierarchical structure into a single string(將層次結構中的類別連接成單個字符串的 SQL 語句)
本文介紹了將層次結構中的類別連接成單個字符串的 SQL 語句的處理方法，對大家解決問題具有一定的參考價值，需要的朋友們下面隨著小編來一起學習吧！

問題描述

我已經檢查了這個問題，但這有所不同，因為我的物品可能會掉落在多個父節(jié)點而不是單個節(jié)點下，我有一個額外的映射表，而不是一個表中的所有內容.

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 中的示例.

當然，這種設計的缺點是，如果層次結構發(fā)生變化，則必須將它們更新到每個產品.另一種選擇是將項目存儲到最低級別并使用遞歸 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模板網！

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

相關文檔推薦

Modify Existing decimal places info(修改現有小數位信息)
The correlation name #39;CONVERT#39; is specified multiple times(多次指定相關名稱“CONVERT)
T-SQL left join not returning null columns(T-SQL 左連接不返回空列)
remove duplicates from comma or pipeline operator string(從逗號或管道運算符字符串中刪除重復項)
Change an iterative query to a relational set-based query(將迭代查詢更改為基于關系集的查詢)
concatenate a zero onto sql server select value shows 4 digits still and not 5(將零連接到 sql server 選擇值仍然顯示 4 位而不是 5)
主站蜘蛛池模板： 日韩成人 | 99久9 | 国产免费一二三区 | 精品久久久久久国产 | av资源中文在线天堂 | 观看av | 亚洲在线视频 | 亚洲国产69 | 日韩免费网站 | 精品欧美一区二区三区久久久 | 欧美日韩精品中文字幕 | 日本在线一区二区 | 日本午夜精品 | 亚洲欧洲中文 | 成在线人视频免费视频 | 草久在线| 美女一级a毛片免费观看97 | 中文字幕 视频一区 | 久久er精品 | 99re6在线视频精品免费 | 成人精品一区 | 免费在线观看黄色av | h视频在线观看免费 | 2一3sex性hd| 日本 欧美 三级 高清 视频 | 香蕉久久av| h片在线观看网站 | 91午夜在线 | 在线观看中文字幕dvd播放 | 欧美一区二区三区久久精品视 | 亚洲高清视频在线观看 | 日日躁狠狠躁aaaaxxxx | 亚洲一区精品在线 | 久草视频在线播放 | 国产在线精品一区二区三区 | 亚洲情综合五月天 | 羞羞视频在线观免费观看 | 一区二区三区在线免费观看视频 | 日韩av在线免费 | 国产人成精品一区二区三 | 国产在线精品一区二区三区 |