sql - 两列数据的所有可能组合

Question

我有一个两列 View

Product Id   Tag
----------------------
1            Leather
1            Watch
2            Red
2            Necklace
2            Pearl

我正在尝试获取产品的所有可能的标签组合，如下所示:

1          Leather
1          Leather,Watch
2          Pearl
2          Pearl,Necklace
2          Pearl Necklace,Red
2          Necklace
2          Necklace, Red
2          Red

我发现并窃取了一些 SQL，它们为我提供了所有版本(但不是小版本)的完整列表，如下。

任何想法，都开始让我头疼。虚拟品脱最佳答案。

SELECT ProductId, 
       (SELECT CAST(Tag + ', ' AS VARCHAR(MAX)) 
          FROM ProductByTagView 
         WHERE Product.ProductId = ProductByTagView.ProductId
      order by tag
       FOR XML PATH ('')) AS Tags
FROM Product

Answer 1

这是一种方法。

理论上，每个产品最多可以处理 20 个标签(受数字表大小的限制)，但我没有费心去尝试。在我的桌面上，大约需要 30 秒才能生成带有 16 个标签的单个产品的 65,535 个结果。希望每个产品的实际标签数量会比这个少很多!

IF OBJECT_ID('tempdb..#Nums') IS NULL
BEGIN
CREATE TABLE #Nums
(
i int primary key
)

;WITH 
L0 AS (SELECT 1 AS c UNION ALL SELECT 1),       
L1 AS (SELECT 1 AS c FROM L0 A CROSS JOIN L0 B), 
L2 AS (SELECT 1 AS c FROM L1 A CROSS JOIN L1 B), 
L3 AS (SELECT 1 AS c FROM L2 A CROSS JOIN L2 B),
L4 AS (SELECT 1 AS c FROM L3 A CROSS JOIN L3 B), 
L5 AS (SELECT 1 AS c FROM L4 A CROSS JOIN L4 B), 
Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS i FROM L5)

INSERT INTO #Nums
SELECT TOP 1048576 i FROM Nums;
END


;with ProductTags As
(
SELECT 1 ProductId,'Leather' AS Tag UNION ALL
SELECT 1, 'Watch' UNION ALL
SELECT 2, 'Red' UNION ALL
SELECT 2, 'Necklace' UNION ALL
SELECT 2, 'Pearl'
), NumberedTags AS
(
SELECT 
      ProductId,Tag,
       ROW_NUMBER() OVER (PARTITION BY ProductId ORDER BY Tag) rn,
       COUNT(*) OVER (PARTITION BY ProductId) cn
FROM ProductTags
),
GroupedTags As
(
SELECT ProductId,Tag,i
FROM NumberedTags
JOIN #Nums  on 
               #Nums.i <  POWER ( 2 ,cn)  
           and #Nums.i & POWER ( 2 ,rn-1) > 0
)
SELECT ProductId, 
       STUFF((SELECT CAST(', ' + Tag AS VARCHAR(MAX)) 
          FROM GroupedTags g2
         WHERE g1.ProductId = g2.ProductId and g1.i = g2.i
      ORDER BY Tag
       FOR XML PATH ('')),1,1,'') AS Tags
FROM GroupedTags g1
GROUP BY ProductId, i
ORDER BY ProductId, i

返回

ProductId   Tags
----------- ------------------------------
1            Leather
1            Watch
1            Leather, Watch
2            Necklace
2            Pearl
2            Necklace, Pearl
2            Red
2            Necklace, Red
2            Pearl, Red
2            Necklace, Pearl, Red