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

如果产品组是(say)“Size”或“Colour”,产品使用将是(say)“Large”、“Extra Great”和“Red”、“黑”等。

基本上,我想找到每个产品的所有可能的产品选择组合。例如,关于产品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

每个产品选择组只有一种产品选择权,供各行使用。 I.e. 大型和外观是相互排斥的。

理想的情况是,与这些数值一样,我对每种产品(22,21,25年)形成一种单一的VARCHAR。

如何在2005年Kall服务器中做到这一点?

增编

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

实用读物:

Ranking Functions (Transact-SQL): DENSE_RANK (Transact-SQL)

Answer 2

Sample:

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

Output:

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

Answer 3

结构取决于您的表层结构。如果各栏储存在单独的表格中,那么简单的漫画产品(没有标准)应当产生预期结果。

友情链接