两列数据的所有可能组合 [英] All possible combinations for two column data

问题描述

我有两列视图

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

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

I'm trying to get all possible combinations of tags for a product as such:

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

我发现并窃取了一些SQL，这些SQL可以为我提供所有(但不是小版本)完整列表，如下所示.

I've found and stolen some SQL that give me the complete list for all but not the small versions, its below.

任何想法，都开始使我的头部受伤.虚拟品脱的最佳答案.

Any ideas, it's started to make my head hurt. A virtual pint for the best answer.

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

推荐答案

这是一种方法.

理论上，每个产品最多可以处理20个标签(受数字表的大小限制)，但我没有尝试过.在我的台式机上，花了30秒钟才能得出带有16个标签的单个产品的65,535个结果.希望每个产品的实际标签数量将比这个少很多！

In theory it can cope with up to 20 tags per Product (limited by the size of the numbers table) I didn't bother attempting that though. On my desktop it took about 30 seconds to churn out the 65,535 results for a single product with 16 tags. Hopefully your actual number of tags per product will be a lot less than that!

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

这篇关于两列数据的所有可能组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！