SQLLEFTJOIN到許多類別-有解無憂

假設以下簡單場景，其中產品行連接到一個主要類別、子類別和子子類別。

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, 2' 能夠檢索它們的名字（我試過LEFT JOIN @CATS AS cat1 ON cat1.ID IN prod.CAT但它不起作用）
創建相應貓、子貓、子子貓的三元組，即。為了

貓 '1, 2'
子貓 '12, 17'
子子類 '239, 372'

(after retrieving the appropriate names) create pipe-separated category routes like 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

So, for a row like (1, 'NIKE MILLENIUM', '1, 2', '10, 12', '100, 135'),

I would like to get the following result

ID	DESCRIPTION	CATEGORIES
1	NIKE MILLENIUM	MEN > FOOTWEAR > RUNNING @ WOMEN > OUTERWEAR > FLEECE (I had to use @ as the delimiter of the two triplets because pipe messed with the table's columns)

In case the user stupidly stores more cat IDs than subcat IDs, or sub-subcat IDs, the query should just match the ones that have a corresponding position match, ie for

cats '1, 2'
subcats '12'
sub-subcats '239, 372'

it should just create one triplet, like name of 1 > name of 12 > name of 239

uj5u.com熱心網友回復：

STRING_SPLIT() 不承諾以特定順序回傳值，因此在這種情況下它不起作用，因為順序位置很重要。

使用OPENJSON()將字串拆分為單獨的行以確保以相同的順序回傳值。
OPENJSON()還回傳一個key欄位，因此您可以加入每個分組中的行號。您將需要一個，INNER JOIN因為您的要求是該“列”中的所有值都必須存在。
使用STUFF()組裝各種貓> 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,''), '&gt;', '>') CATEGORIES
from a

uj5u.com熱心網友回復：

由于對舊技術的改變，完全不同的答案。我認為我的原始答案對使用當前 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,''), '&gt;', '>') CATEGORIES
from a

uj5u.com熱心網友回復：

好吧，這應該可行，我將您的字符“>”更改為“-”，只是為了更簡單地查看資料。

你的桌子的設計并不完美，但第一次嘗試幾乎從來沒有。

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/

轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/313285.html

標籤：sql sql-server tsql sql-server-2014

上一篇：將子查詢結果應用于外部查詢中的每一行

下一篇：字串匹配和排序邏輯