SQL左连接到多个类别

Question

假设下面的简单场景，其中一个产品行连接到一个主类别、子类别和子类别。

DECLARE @PRODUCTS TABLE (ID int, DESCRIPTION varchar(50), CAT varchar(30), SUBCAT varchar(30), SUBSUBCAT varchar(30));
INSERT @PRODUCTS (ID, DESCRIPTION, CAT, SUBCAT, SUBSUBCAT) VALUES
(1, 'NIKE MILLENIUM', '1', '10', '100'),
(2, 'NIKE CORTEZ', '1', '12', '104'),
(3, 'ADIDAS PANTS', '2', '27', '238'),
(4, 'PUMA REVOLUTION 5', '3', '35', '374'),
(5, 'SALOMON SHELTER CS', '4', '15', '135'),
(6, 'NIKE EBERNON LOW', '2', '14', '157');
DECLARE @CATS TABLE (ID int, DESCR varchar(100));
INSERT @CATS (ID, DESCR) VALUES
(1, 'MEN'),
(2, 'WOMEN'),
(3, 'UNISEX'),
(4, 'KIDS'),
(5, 'TEENS'),
(6, 'BACK TO SCHOOL');
DECLARE @SUBCATS TABLE (ID int, DESCR varchar(100));
INSERT @SUBCATS (ID, DESCR) VALUES
(10, 'FOOTWEAR'),
(12, 'OUTERWEAR'),
(14, 'SWIMWEAR'),
(15, 'HOODIES'),
(27, 'CLOTHING'),
(35, 'SPORTS');
DECLARE @SUBSUBCATS TABLE (ID int, DESCR varchar(100));
INSERT @SUBSUBCATS (ID, DESCR) VALUES
(100, 'RUNNING'),
(104, 'ZIP TOPS'),
(135, 'FLEECE'),
(157, 'BIKINIS'),
(238, 'PANTS'),
(374, 'JOGGERS');

SELECT prod.ID,
prod.DESCRIPTION,
CONCAT(cat1.DESCR, ' > ', cat2.DESCR, ' > ', cat3.DESCR) AS CATEGORIES
FROM @PRODUCTS AS prod
LEFT JOIN @CATS AS cat1 ON cat1.ID = prod.CAT
LEFT JOIN @SUBCATS AS cat2 ON cat2.ID = prod.SUBCAT
LEFT JOIN @SUBSUBCATS AS cat3 ON cat3.ID = prod.SUBSUBCAT;

现在假设@PRODUCTS表上的外键不仅仅是它们各自表的索引。它们是多个类别、子类别和子类别的逗号分隔索引，就像这里一样。

DECLARE @PRODUCTS TABLE (ID int, DESCRIPTION varchar(50), CAT varchar(30), SUBCAT varchar(30), SUBSUBCAT varchar(30));
INSERT @PRODUCTS (ID, DESCRIPTION, CAT, SUBCAT, SUBSUBCAT) VALUES
(1, 'NIKE MILLENIUM', '1, 2', '10, 12', '100, 135'),
(2, 'NIKE CORTEZ', '1, 5', '12, 15', '104, 374'),
(3, 'ADIDAS PANTS', '2, 6', '27, 35', '238, 374');
DECLARE @CATS TABLE (ID int, DESCR varchar(100));
INSERT @CATS (ID, DESCR) VALUES
(1, 'MEN'),
(2, 'WOMEN'),
(3, 'UNISEX'),
(4, 'KIDS'),
(5, 'TEENS'),
(6, 'BACK TO SCHOOL');
DECLARE @SUBCATS TABLE (ID int, DESCR varchar(100));
INSERT @SUBCATS (ID, DESCR) VALUES
(10, 'FOOTWEAR'),
(12, 'OUTERWEAR'),
(14, 'SWIMWEAR'),
(15, 'HOODIES'),
(27, 'CLOTHING'),
(35, 'SPORTS');
DECLARE @SUBSUBCATS TABLE (ID int, DESCR varchar(100));
INSERT @SUBSUBCATS (ID, DESCR) VALUES
(100, 'RUNNING'),
(104, 'ZIP TOPS'),
(135, 'FLEECE'),
(157, 'BIKINIS'),
(238, 'PANTS'),
(374, 'JOGGERS');

SELECT prod.ID,
prod.DESCRIPTION
--CONCAT(cat1.DESCR, ' > ', cat2.DESCR, ' > ', cat3.DESCR) AS CATEGORIES
FROM @PRODUCTS AS prod
--LEFT JOIN @CATS AS cat1 ON cat1.ID = prod.CAT
--LEFT JOIN @SUBCATS AS cat2 ON cat2.ID = prod.SUBCAT
--LEFT JOIN @SUBSUBCATS AS cat3 ON cat3.ID = prod.SUBSUBCAT;

在这种情况下，我想实现以下目标:

1. 能够检索猫、子猫、子猫的各自名称，即。对于猫' 1,2 '能够检索他们的名字(我试过LEFT JOIN @CATS AS cat1 ON cat1.ID IN prod.CAT，但它不起作用)
2. 创建对应的猫、子猫、子猫的三元组，即:

• cats ' 1,2 '
• 子cat ' 12,17 '
• subcats '239, 372'

(检索适当的名称后)创建管道分隔的类别路由，如name of cat 1>name of subcat 12祝辞name of sub-subcat 239|name of cat 2>name of subcat 17祝辞name of sub-subcat 372

对于(1, 'NIKE MILLENIUM', '1, 2', '10, 12', '100, 135'),

这样的行我想得到以下结果

IDtbody> <<tr>

描述	CATEGORIES
1	耐克千禧年	男人比;鞋子比;跑步@ WOMEN>外套在羊毛(我不得不使用@作为分隔符的两个三胞胎因为管乱表的列)

STRING_SPLIT()不承诺以特定的顺序返回值，因此在这种情况下，由于顺序位置问题，它将无法工作。

使用OPENJSON()将字符串拆分为单独的行，以确保以相同的顺序返回值。
OPENJSON()还返回key字段，因此您可以根据每个分组中的行号进行连接。您将需要一个INNER JOIN，因为您的要求是该"列"中的所有值必须存在。
使用STUFF()组装各种cat> subcat> subsubcat值。

DECLARE @PRODUCTS TABLE (ID int, DESCRIPTION varchar(50), CAT varchar(30), SUBCAT varchar(30), SUBSUBCAT varchar(30));
INSERT @PRODUCTS (ID, DESCRIPTION, CAT, SUBCAT, SUBSUBCAT) VALUES
(1, 'NIKE MILLENIUM', '1, 2', '10, 12', '100, 135'),
(2, 'NIKE CORTEZ', '1, 5', '12, 15', '104, 374'),
(3, 'ADIDAS PANTS', '2, 6, 1', '27, 35, 10', '238, 374, 100'),
(4, 'JOE THE PLUMBER JEANS', '1, 5', '27', '238, 374');
DECLARE @CATS TABLE (ID int, DESCR varchar(100));
INSERT @CATS (ID, DESCR) VALUES
(1, 'MEN'),
(2, 'WOMEN'),
(3, 'UNISEX'),
(4, 'KIDS'),
(5, 'TEENS'),
(6, 'BACK TO SCHOOL');
DECLARE @SUBCATS TABLE (ID int, DESCR varchar(100));
INSERT @SUBCATS (ID, DESCR) VALUES
(10, 'FOOTWEAR'),
(12, 'OUTERWEAR'),
(14, 'SWIMWEAR'),
(15, 'HOODIES'),
(27, 'CLOTHING'),
(35, 'SPORTS');
DECLARE @SUBSUBCATS TABLE (ID int, DESCR varchar(100));
INSERT @SUBSUBCATS (ID, DESCR) VALUES
(100, 'RUNNING'),
(104, 'ZIP TOPS'),
(135, 'FLEECE'),
(157, 'BIKINIS'),
(238, 'PANTS'),
(374, 'JOGGERS');
;
with prod as (
SELECT p.ID,
p.DESCRIPTION
--CONCAT(cat1.DESCR, ' > ', cat2.DESCR, ' > ', cat3.DESCR) AS CATEGORIES
, c.value as CatId
, c.[key] as CatKey
, sc.value as SubCatId
, sc.[key] as SubCatKey
, ssc.value as SubSubCatId
, ssc.[key] as SubSubCatKey
FROM @PRODUCTS p
cross apply OPENJSON(CONCAT('["', REPLACE(cat, ', ', '","'), '"]')) c
cross apply OPENJSON(CONCAT('["', REPLACE(subcat, ', ', '","'), '"]')) sc
cross apply OPENJSON(CONCAT('["', REPLACE(subsubcat, ', ', '","'), '"]')) ssc
where c.[key] = sc.[key]
and c.[key] = ssc.[key]
)
, a as (
select p.ID
, p.DESCRIPTION
, c.DESCR + ' > ' + sc.DESCR + ' > ' + ssc.DESCR as CATEGORIES
, p.CatKey
from prod p
inner join @CATS c on c.ID = p.CatId
inner join @SUBCATS sc on sc.ID = p.SubCatId
inner join @SUBSUBCATS ssc on ssc.ID = p.SubSubCatId
)
select DISTINCT ID
, DESCRIPTION
, replace(STUFF((SELECT distinct ' | ' + a2.CATEGORIES
from a a2
where a.ID = a2.ID
FOR XML PATH(''))
,1,2,''), '>', '>') CATEGORIES
from a

完全不同的答案，因为旧技术的变化。我认为我原来的答案对使用当前SQL Server版本的人来说仍然是好的，所以我不想删除它。

我不记得从哪里得到这个函数了。当我今天发现它时，它被命名为split_delimiter。我更改了名称，添加了一些注释，并加入了一个长度超过一个字符的分隔符的功能。

CREATE FUNCTION [dbo].[udf_split_string](@delimited_string VARCHAR(8000), @delimiter varchar(10))
RETURNS TABLE AS
RETURN
WITH cte10(num) AS (    --  10 rows
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
)                         
, cte100(num) AS (  --  100 rows
SELECT 1 
FROM   cte10 t1, cte10 t2
)
, cte10000(num) AS (    --  10000 rows
SELECT 1 
FROM   cte100 t1, cte100 t2
)
, cte1(num) AS (    --  1 row per character
SELECT TOP (ISNULL(DATALENGTH(@delimited_string), 0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) 
FROM   cte10000
)
, cte2(num) AS (    --  locations of strings
SELECT  1
UNION ALL
SELECT t.num + len(replace(@delimiter, ' ', '_'))
FROM   cte1 t
WHERE  SUBSTRING(@delimited_string, t.num, len(replace(@delimiter, ' ', '_'))) = @delimiter
)
, cte3(num, [len]) AS (
SELECT t.num
, ISNULL(NULLIF(CHARINDEX(@delimiter, @delimited_string, t.num), 0) - t.num, 8000)
FROM  cte2 t
)
SELECT [Key]   = ROW_NUMBER() OVER (ORDER BY t.num)
, [Value] = SUBSTRING(@delimited_string, t.num, t.[len])
FROM cte3 t;
GO

DECLARE @PRODUCTS TABLE (ID int, DESCRIPTION varchar(50), CAT varchar(30), SUBCAT varchar(30), SUBSUBCAT varchar(30));
INSERT @PRODUCTS (ID, DESCRIPTION, CAT, SUBCAT, SUBSUBCAT) VALUES
(1, 'NIKE MILLENIUM', '1, 2', '10, 12', '100, 135'),
(2, 'NIKE CORTEZ', '1, 5', '12, 15', '104, 374'),
(3, 'ADIDAS PANTS', '2, 6, 1', '27, 35, 10', '238, 374, 100'),
(4, 'JOE THE PLUMBER JEANS', '1, 5', '27', '238, 374');
DECLARE @CATS TABLE (ID int, DESCR varchar(100));
INSERT @CATS (ID, DESCR) VALUES
(1, 'MEN'),
(2, 'WOMEN'),
(3, 'UNISEX'),
(4, 'KIDS'),
(5, 'TEENS'),
(6, 'BACK TO SCHOOL');
DECLARE @SUBCATS TABLE (ID int, DESCR varchar(100));
INSERT @SUBCATS (ID, DESCR) VALUES
(10, 'FOOTWEAR'),
(12, 'OUTERWEAR'),
(14, 'SWIMWEAR'),
(15, 'HOODIES'),
(27, 'CLOTHING'),
(35, 'SPORTS');
DECLARE @SUBSUBCATS TABLE (ID int, DESCR varchar(100));
INSERT @SUBSUBCATS (ID, DESCR) VALUES
(100, 'RUNNING'),
(104, 'ZIP TOPS'),
(135, 'FLEECE'),
(157, 'BIKINIS'),
(238, 'PANTS'),
(374, 'JOGGERS');
;
with prod as (
SELECT p.ID,
p.DESCRIPTION
, c.value as CatId
, c.[key] as CatKey
, sc.value as SubCatId
, sc.[key] as SubCatKey
, ssc.value as SubSubCatId
, ssc.[key] as SubSubCatKey
FROM @PRODUCTS p
cross apply dbo.udf_split_string(cat, ', ') c
cross apply dbo.udf_split_string(subcat, ', ') sc
cross apply dbo.udf_split_string(subsubcat, ', ') ssc
where c.[key] = sc.[key]
and c.[key] = ssc.[key]
)
, a as (
select p.ID
, p.DESCRIPTION
, c.DESCR + ' > ' + sc.DESCR + ' > ' + ssc.DESCR as CATEGORIES
, p.CatKey
from prod p
inner join @CATS c on c.ID = p.CatId
inner join @SUBCATS sc on sc.ID = p.SubCatId
inner join @SUBSUBCATS ssc on ssc.ID = p.SubSubCatId
)
select DISTINCT ID
, DESCRIPTION
, replace(STUFF((SELECT distinct ' | ' + a2.CATEGORIES
from a a2
where a.ID = a2.ID
FOR XML PATH(''))
,1,2,''), '>', '>') CATEGORIES
from a

应该可以了，我改了你的字符">"为"产生绯闻;只是为了看数据更简单。

你的表的设计是不完美的，但第一次尝试几乎永远不会完美。

select mainp.ID, mainp.DESCRIPTION, stuff(ppaths.metapaths, len(ppaths.metapaths),1,'') metalinks
from @PRODUCTS mainp
cross apply(
select
(select 
c.DESCR + '-' + sc.DESCR + '-' + sbc.DESCR + '|'
from @PRODUCTS p    
cross apply (select row_number() over(order by Value) id, Value from split(p.CAT, ','))cat_ids
inner join @cats c on c.ID = cat_ids.Value
cross apply (select row_number() over(order by Value) id, Value from split(p.SUBCAT, ','))subcat_ids
inner join @SUBCATS sc on sc.ID = subcat_ids.Value
and subcat_ids.id = subcat_ids.id
cross apply (select row_number() over(order by Value) id, Value  from split(p.SUBSUBCAT, ','))subsubcat_ids
inner join @SUBSUBCATS sbc on sbc.ID = subsubcat_ids.Value
and subsubcat_ids.id = subcat_ids.id
where p.id = mainp.ID
for xml path('')) metapaths
) ppaths

拆分函数的链接https://desarrolladores.me/2014/03/sql-server-funcion-split-para-dividir-un-string/