MySQL获得某些行的所有可能的组合 [英] mySQL get all possible combinations of certain rows

问题描述

我在mySQL中有一个奇怪的请求.我发现有很多方法可以通过添加更多联接来对成对的组合或某个其他数量的组合进行此操作，但是我想知道是否存在针对任意数量的组合进行此操作的动态方法.

I have a strange request in mySQL. I found many ways to do this for pairs of combinations or a certain other number by adding more joins, but I am wondering if there is a dynamic way of doing it for any number of combinations.

要说明我是否有一个表，表中有1列(column_id)和(column_text)

To explain if I have a table table has 1 column (column_id) and (column_text)

Id | Text
--------
1  | A
2  | B
3  | B
4  | B
5  | A

然后通过运行带有参数A的GetCombinations过程应产生:

Then by running a procedure GetCombinations with parameter A should yield:

CombinationId | Combinations
---------------------------
1             |      1
2             |      5
3             |      1,5

通过运行带有参数B的GetCombinations过程应产生:

by running a procedure GetCombinations with parameter B should yield:

CombinationId | Combinations
---------------------------
1             |      2
2             |      3
3             |      4
4             |      2,3
5             |      2,4
6             |      3,4
7             |      2,3,4

显然，数字越大，那么我期望结果呈指数增长.

Obviously the larger the number, then I expect an exponential increase of results.

这样的查询是否可能?我所能找到的就是使用Joins将结果的长度限制为Joins数量的结果.

Is such a query even possible? All I could find was results using Joins limiting the length of each result to the number of Joins.

谢谢

我在此处找到了一篇文章，但组合应该很小(最多20个左右).以我的100个组合为例，我计算出它将产生:9426890448883247745626185743057242473809693764078951663494238777294707070023223798882976159207729119823605850588608460429412647567360000000000000000000000000099行(哈哈)

I have found an article here but the maximum number of combinations should be small (max 20 or so). In my case with a 100 combinations I calculated that it would produce: 9426890448883247745626185743057242473809693764078951663494238777294707070023223798882976159207729119823605850588608460429412647567360000000000000000000099 rows (lol)

所以我将答案归为不可行

So I will classify my answer as infeasible

但是有没有办法以最多2种组合获得此结果?

However is there a way to get this result with max 2 combinations?

CombinationId | Combinations
---------------------------
1             |      2
2             |      3
3             |      4
4             |      2,3
5             |      2,4
6             |      3,4

我找到了一个使用JOIN获取所有组合的查询，但是我不确定如何生成组合ID以及如何获取单个行.

I have found a query to get all combinations using JOIN but I am not sure how to produce the combination id and also how to get the individual rows.

使用

SELECT @rownum := @rownum + 1 AS 'CombinationId'
cross join (select @rownum := 0) r

我用UNION ALL进行了查询

And I did the query with UNION ALL

推荐答案

您要执行的操作是生成

What you are trying to do is to generate the Power Set of the set of all elements with field Text == <parameter>. As you already found out, this number grows exponentially with the length of the input array.

如果您可以用其他语言(例如php)解决问题，请查看以下内容:

If you can solve it in other language (say, php), take a look at this:

在PHP中查找数组的子集

这篇关于MySQL获得某些行的所有可能的组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！