T-SQL 返回表的所有三向组合 [英] T-SQL Return All 3-Way Combinations of a Table

问题描述

我有一个相对简单的问题，但似乎无法找到解决方案.这是我的桌子的样子:

I have a relatively simple problem but cannot seem to find the solution. This is how my table looks like:

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

现在，我想找到所有可能的 3 路组合.每篇文章都必须包含在每个组中(4711、4712、4713).对于上面的示例，我们将获得 6 个组合对和 18 个数据集.结果应如下所示:

Now, I want to find all possible 3-way combinations. Each article has to be included in each group (4711, 4712, 4713). For the example above, we will get 6 combination pairs and 18 datasets. The result should look like as follows:

+----------------+---------+----------+
| 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        |
+----------------+---------+----------+

我非常感谢您的帮助.

推荐答案

我觉得把每个组合排成一行更容易:

I think it is easier to put each combination in a row:

select row_number() over () as combination_nr,
       t1.article, t1.supplier,
       t2.article, t2.supplier,
       t3.article, t3.supplier
from t t1 join
     t t2
     on t2.article > t1.article 
     t t3
     on t3.article > t2.article;

如果您确实需要，您可以将其拆分为单独的行.

You can unpivot this into separate rows if you really need to.

这篇关于T-SQL 返回表的所有三向组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！