問題描述

在另一個問題中，我詢問了表格的所有可能的 3 向組合，假設(shè)有 3 篇文章.在這個問題中，我想進一步擴展問題以返回具有 n 個不同文章的表的所有 n 向組合.一篇文章可以有多個供應(yīng)商.我的目標是有一組組合，每個組都有每篇文章.

In a different question, I asked about all possible 3-way combinations of a table, assuming that there are 3 articles. In this question, I would like to further extend the problem to return all n-way combinations of a table with n distinct articles. One article can have multiple suppliers. My goal is to have groups of combinations with each group having each article.

下面是一個示例表，但請記住，可能不止這 3 篇文章.

Below is a sample table but keep in mind that there could be more than those 3 articles.

+---------+----------+
| Article | Supplier |
+---------+----------+
|    4711 | A        |
|    4712 | B        |
|    4712 | C        |
|    4712 | D        |
|    4713 | C        |
|    4713 | E        |
+---------+----------+

對于上面的示例，18 個數(shù)據(jù)集可能有 6 個組合對.下面是上面示例的解決方案應(yīng)該是什么樣子:

For the example above, there would be 6 combination pairs possible with 18 datasets. Below is how the solution for the example above is supposed to look like:

+----------------+---------+----------+
| combination_nr | article | supplier |
+----------------+---------+----------+
|              1 |    4711 | A        |
|              1 |    4712 | B        |
|              1 |    4713 | C        |
|              2 |    4711 | A        |
|              2 |    4712 | B        |
|              2 |    4713 | E        |
|              3 |    4711 | A        |
|              3 |    4712 | C        |
|              3 |    4713 | C        |
|              4 |    4711 | A        |
|              4 |    4712 | D        |
|              4 |    4713 | E        |
|              5 |    4711 | A        |
|              5 |    4712 | D        |
|              5 |    4713 | C        |
|              6 |    4711 | A        |
|              6 |    4712 | D        |
|              6 |    4713 | E        |
+----------------+---------+----------+

我在另一個問題中問這個，因為在另一個問題中我沒有指定我需要這是動態(tài)的，而不僅僅是上面的 3 篇文章.在這里您可以找到舊問題.

I am asking this in a different question because in the other question I have not specified that I need this to be dynamic and not only the 3 articles from above. Here you can find the old question.

推薦答案

要創(chuàng)建此結(jié)果，您需要確定兩件事:

To create this result you'll need to determine two things:

• 一組包含可能組合數(shù)量的 ID 的數(shù)據(jù).
• 一種確定商品/供應(yīng)商對何時對應(yīng)于組合 ID 的方法.

為了找出組合的集合，您需要先弄清楚組合的數(shù)量.為此，您需要確定為每件商品設(shè)置的每個供應(yīng)商的規(guī)模.使用 count 聚合函數(shù)可以輕松獲取大小，但為了找出組合，我需要所有值的乘積，這并不容易.幸運的是，在另一個 SO 問題中有一個關(guān)于如何做到這一點的答案.

In order to figure out the set of combinations, you need to first figure out the number of combinations. To do that, you need to figure out the size of each supplier set for each article. Getting the size is accomplished easily with a count aggregate function, but in order to figure out the combinations, I needed a product of all the values, which is not easily done. Fortunately there was an answer on how to do this in another SO questions.

既然確定了組合的數(shù)量，就必須生成 id.在 TSQL 中沒有很好的方法可以做到這一點.我最終使用了一個簡單的遞歸 CTE.這樣做的缺點是最多只能產(chǎn)生 32767 個組合.如果您需要更多，還有其他方法可以產(chǎn)生這些值.

Now that the number of combinations is determined, the ids have to be generated. There is no great way to do this in TSQL. I ended up using a simple recursive CTE. This has the drawback of only being able to produce up to 32767 combos. There are other methods to produce these values if you're going to need more.

為了確定物品/供應(yīng)商對何時與組合對齊，我使用 ROW_NUMBER 窗口函數(shù)按文章分區(qū)并按供應(yīng)商排序，以獲得每對的序列號.然后它使用了一個老技巧，通過序列號使用組合數(shù)的模數(shù)來確定是否顯示一對.

To determine when an article/supplier pair lines up with a combination I use ROW_NUMBER window function partition by Article and sorted by Supplier to get a sequence number for each pair. Then it's using an old trick of using Modulo of the combination number by the sequence number to determine if a pair is displayed.

仍然存在一個問題，即無法保證冗余對不會配對在一起.為了解決這個問題，添加了一個 CTE，用于計算出現(xiàn)在文章之前的可能組合的數(shù)量.目的是在顯示下一個順序之前，將后面文章中的值重復(fù)組合的次數(shù).我稱之為乘數(shù)(即使我用它除以 comboId)，這將確保不同的結(jié)果.

There is still an issue in that there is no guarantee that redundant pairs won't be paired together. In order to address this a CTE was added that calculates the number of possible combinations that come before an article. The intention is so that a value in a later article is repeated the number of times for a combination before the next in sequence is displayed. I called it multiplier (even though I divide the comboId by it) and this is what will ensure distinct results.

WITH ComboId AS (
    -- Product from https://stackoverflow.com/questions/3912204/why-is-there-no-product-aggregate-function-in-sql
    SELECT 0 [ComboId], exp (sum (log (SequenceCount))) MaxCombos
    FROM (
        SELECT COUNT(*) SequenceCount
        FROM src 
        GROUP BY Article
    ) x
    UNION ALL
    SELECT ComboId + 1, MaxCombos
    FROM ComboId
    WHERE ComboId + 1 < MaxCombos
)
, Multiplier AS (
    SELECT s.Article
        , COALESCE(exp (sum (log (SequenceCount)) OVER (ORDER BY Article ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)), 1) [Multiplier]
    FROM (
        SELECT Article, COUNT(*) SequenceCount
        FROM src
        GROUP BY Article
    ) s
)
, Sequenced AS (
    SELECT s.Article, s.Supplier, m.Multiplier
        , ROW_NUMBER() OVER (PARTITION BY s.Article ORDER BY s.Supplier) - 1 ArtSupplierSeqNum
        , COUNT(*) OVER (PARTITION BY s.Article) MaxArtSupplierSeqNum
    FROM src s
    INNER JOIN Multiplier m ON m.Article = s.Article
)
SELECT c.ComboId + 1 [ComboId], s.Article, s.Supplier
FROM ComboId c
INNER JOIN Sequenced s ON s.ArtSupplierSeqNum = CAST(c.ComboId / Multiplier as INT) % s.MaxArtSupplierSeqNum
ORDER BY ComboId, Article
OPTION (MAXRECURSION 32767)

這篇關(guān)于T-SQL 返回表的所有組合的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！