SQL:查找产品选项数据的所有可能组合/排列

Question

我有以下产品数据(针对在线商店)：

ProductId  ProductOptionGroupId  ProductOptionId
26         1                     13
26         1                     12
44         1                     22
44         1                     23
44         2                     20
44         2                     21
44         3                     25
44         3                     24

其中ProductOptionGroup是“大”或“色”，而ProductOption是“大”、“特大号”和“红”、“黑”等。

基本上，我想为每个产品找到所有可能的产品选项组合。例如，对于产品44，我希望：

22, 20, 25  (Large, Black, Cotton)
22, 20, 24  (Large, Black, Nylon)
22, 21, 25  (Large, Red, Cotton)
22, 21, 24  (Large, Red, Nylon)
23, 20, 25  (Extra Large, Black, Cotton)
23, 20, 24  etc...
23, 21, 25
23, 21, 24

每行的每个产品选项组中只有一个产品选项。也就是说，大尺寸和超大尺寸是相互排斥的。

理想情况下，我希望将这些值连接到每个产品的单个VARCHAR中("22,21,25“等)。

如何在SQL Server 2005中实现这一点？

谢谢

Answer 1

WITH
  data (ProductId, ProductOptionGroupId, ProductOptionId) AS (
    /* defining sample data */
    SELECT 26, 1, 13 UNION ALL
    SELECT 26, 1, 12 UNION ALL
    SELECT 44, 1, 22 UNION ALL
    SELECT 44, 1, 23 UNION ALL
    SELECT 44, 2, 20 UNION ALL
    SELECT 44, 2, 21 UNION ALL
    SELECT 44, 3, 25 UNION ALL
    SELECT 44, 3, 24
  ),
  ranked AS (
    /* ranking the group IDs */
    SELECT
      ProductId,
      ProductOptionGroupId,
      ProductOptionId,
      GroupRank = DENSE_RANK() OVER (PARTITION BY ProductId
                                         ORDER BY ProductOptionGroupId)
    FROM data
  ),
  crossjoined AS (
    /* obtaining all possible combinations */
    SELECT
      ProductId,
      GroupRank,
      ProductVariant = CAST(ProductOptionId AS varchar(250))
    FROM ranked
    WHERE GroupRank = 1
    UNION ALL
    SELECT
      r.ProductId,
      r.GroupRank,
      ProductVariant = CAST(c.ProductVariant + ','
        + CAST(r.ProductOptionId AS varchar(10)) AS varchar(250))
    FROM ranked r
      INNER JOIN crossjoined c ON r.ProductId = c.ProductId
                              AND r.GroupRank = c.GroupRank + 1
  ),
  maxranks AS (
    /* getting the maximum group rank value for every product */
    SELECT
      ProductId,
      MaxRank = MAX(GroupRank)
    FROM ranked
    GROUP BY ProductId
  )
/* getting the max ranked combinations for every product */
SELECT c.ProductId, c.ProductVariant
FROM crossjoined c
  INNER JOIN maxranks m ON c.ProductId = m.ProductId
                       AND c.GroupRank = m.MaxRank

输出：

ProductId   ProductVariant
----------- --------------
26          12
26          13
44          22,20,24
44          22,20,25
44          22,21,24
44          22,21,25
44          23,20,24
44          23,20,25
44          23,21,24
44          23,21,25

有用的读物：

• WITH common_table_expression (Transact-SQL)
• Using Common Table Expressions
• Recursive Queries Using Common Table Expressions
• Ranking Functions (Transact-SQL)：DENSE_RANK (Transact-SQL)

Answer 2

示例：

declare @t table(id int, type1 int, type2 int)

insert @t values(1, 1, 1), (1, 1, 2), (1, 2, 1), (2, 2, 1)

select distinct t1.id, t1.type1, t2.type2
from
(
    select id, type1
    from @t
)t1
full join
(
    select id, type2
    from @t
)t2 on t2.id = t1.id

输出：

id          type1       type2
----------- ----------- -----------
1           1           1
1           1           2
1           2           1
1           2           2
2           2           1

Answer 3

SQL将取决于您的表结构。如果列存储在单独的表中，那么一个简单的笛卡尔乘积(没有条件的连接)应该会产生所需的结果。