All geek questions in one place

MySQL gets all possible combinations of certain rows

I have a strange request in mySQL. I found many ways to do this for pairs of combinations or a specific number, adding more joins, but I wonder if there is a dynamic way to do this for any number of combinations.

To explain if there is a table table, there is 1 column (column_id) and (column_text)

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

Then, by running the GetCombinations procedure with parameter A, you will get:

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

Having executed the GetCombinations procedure with parameter B, it should output:

 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, the more I expect an exponential increase in results.

Is such a request possible? All I could find were results using Joins, limiting the length of each result to the number of Joins.

thanks

UPDATE

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

Therefore, I will classify my answer as impracticable.

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 found a query to get all combinations using JOIN, but I'm not sure how to create a combination identifier, or how to get individual rows.

UPDATE 2

Solved it using

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

And I made a request with UNION ALL

+5
source share
1 answer

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

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

Finding subsets of an array in PHP

+1
source

Source: https://habr.com/ru/post/1207138/

More articles:


All Articles