T-SQL 返回表的所有三向组合
我有一个相对简单的问题，但似乎无法找到解决方案.这是我的桌子的样子:
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.
相关文章