如何在SQL中生成所有可能的数据组合? [英] How to generate all possible data combinations in SQL?

问题描述

我正在开发一个商店网站.我有一个数据库表 ProductOption，它表示适用于产品的各种选项(尺寸、颜色等):

I'm developing a shop web site. I have a database table, ProductOption, that represents the various options that are applicable to a product (Sizes, Colours etc):

ProductOptionId
ProductOptionGroupId
ProductId
Value

(为了简洁起见，我已经简化了)

(I've simplified this for brevity)

ProductOptionGroupId 链接到 ProductOptionGroup 表；其中一组是尺寸、颜色等，而值是，嗯，值 - 黑色、红色、大、小等

ProductOptionGroupId links to the ProductOptionGroup table; where a group is Size, Colour etc, and the Value is, well, the value - Black, Red, Large, Small etc.

我需要为数据库中的每个产品生成所有可能的产品选项组合，受组限制.想象一下产品 1 的以下数据(其中组 1=颜色，2=尺寸和 3=长度):

I need to generate all possible combinations of product options, restricted by group, for each product in the database. Imagine the following data for product 1 (where group 1=Colour, 2=Size and 3=Length):

ProductOptionId, ProductOptionGroupId, Value
1, 1, Red
2, 1, Black
3, 1, Green
4, 2, Large
5, 2, Small
6, 3, Long
7, 3, Short

我需要生成代表以下内容的数据:

I'd need to generate data representing the following:

Red, Large, Long
Black, Large, Long
Green, Large, Long
Red, Small, Long
Black, Small, Long
Green, Small, Long
Red, Large, Short
Black, Large, Short
Green, Large, Short
Red, Small, Short
Black, Small, Short
Green, Small, Short

...基本上每个可能的组合，在组内.

...basically every possible combination, within the groups.

每个产品可以有任意数量的组和任意数量的产品选项.我需要能够仅通过提前知道 ProductId 来生成结果(即为我提供此产品的所有产品选项组合).

There can be any number of groups, and any number of products options for each product. I need to be able to generate the results by only knowing the ProductId ahead of time (i.e. give me all product option combinations for this product).

如何使用 SQL Server 2005 实现这一点?

How can I achieve this, using SQL Server 2005?

谢谢

推荐答案

好吧，你在那里遇到了一个问题，特别是如果你不知道哪些组提前附加到哪些产品.在我看来，您将需要在 sql 中尽量避免两件最重要的事情:游标和动态 sql.在使用以下解决方案之前，您应该查看此链接动态 SQL 的诅咒和祝福.然后，你可以试试这个:

Well, you are in kinda of a problem there, specially if you don't know wich groups are attached to wich products ahead of time. It appears to me that you are gonna need two of the biggest things a try to avoid in sql: cursors, and dynamic sql. Before using the following solution, you should take a look at this link The Curse and Blessings of Dynamic SQL. Then, you can try this:

DECLARE @ProductId INT, @Query NVARCHAR(MAX), @ProductOptionGroupId INT
SET @ProductId = 1
SET @Query = ''

DECLARE CC CURSOR FOR
SELECT DISTINCT ProductOptionGroupId
FROM YourTable
WHERE ProductId = @ProductId

OPEN CC
FETCH NEXT FROM CC INTO @ProductOptionGroupId
WHILE @@FETCH_STATUS = 0
BEGIN
    SET @Query = @Query + '(SELECT DISTINCT Value FROM YourTable WHERE ProductOptionGroupId='+CAST(@ProductOptionGroupId AS VARCHAR)+'AND ProductId='+CAST(@ProductId AS VARCHAR)+
                 +') AS Table' + CAST(@ProductOptionGroupId AS VARCHAR)+' CROSS JOIN '

    FETCH NEXT FROM CC INTO @ProductOptionGroupId
END
CLOSE CC
DEALLOCATE CC
SET @Query = 'SELECT * FROM ' + LEFT(@Query,LEN(@Query)-10)

PRINT @Query
EXEC sp_executesql @Query

告诉我进展如何.

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