mysql 某组合出现一次,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.

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

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

CombinationId | Combinations

---------------------------

1 | 1

2 | 5

3 | 1,5

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.

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.

Thank you

UPDATE

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

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

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.

UPDATE 2

Solved it using

SELECT @rownum := @rownum + 1 AS 'CombinationId'

cross join (select @rownum := 0) r

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 == . As you already found out, this number grows exponentially with the length of the input array.

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