SQL - find all possible combinations

Question

I need help finding all possible combinations of values ​​in the database, for example, I have this table:

ITEM_SET Support I1 0.244999999 I2 0.274999999 I3 0.258333333 I4 0.103333333 

I need to find every possible combination:

 I1,I2 I1,I2,I3 I1,I2,I3,I4 I1,I3 I1,I2,I4 I1,I4 I1,I3,I4 I2,I3 I2,I3,I4 I2,I4 I3,I4 

* Please note that this formatting is read-only, since I only need a list of possible combinations, such as this table:

 ITEMSET I1 I2 I3 I4 I1,I2 I1,I3 I1,I4 I2,I3 I2,I4 I3,I4 I1,I2,I3 I1,I2,I4 I1,I3,I4 I2,I3,I4 I1,I2,I3,I4 

Answer 1

What you offer is n! combination of all elements for lengths 1-n. Ignoring the possibility of using a code generator to create your elements, you can do something similar for each combination (in MySQL):

One item:

SELECT item from ITEM_SET;

Two elements:

SELECT one.item,two.item from ITEM_SET as one, ITEM_SET as two where one.item != two.item;

Three elements:

SELECT one.item,two.item,three.item from ITEM_SET as one, ITEM_SET as two, ITEM_SET as three where one.item != two.item and one.item != three.item and two.item != three.item;

Rinse and repeat. To be pedantic, I define ITEM_SET as the name and element of the table as my attribute, which is a more meaningful composite table.

This and the related question are code odors for me. If you go over all permutations of elements programmatically for all candidate answers, then there will probably be a much simpler algorithm to solve your problem. Given your other question , is directly related to this, maybe you can offer additional background information?

Answer 2

One of the simplest algorithms for generating combinations is bit counting.
Pseudo code

 N items, indexed 1-N for i=1 to 2^N-1 for each bit in i if bit is set, output item[i] 

Example for N = 4:

 N = 4, 2^4 = 16 i = 1: binary = 00000001 -> output I1 i = 2: binary = 00000010 -> output I2 i = 3: binary = 00000011 -> output I1, I2 i = 4: binary = 00000100 -> output I3 i = 6: binary = 00000101 -> output I1, I3 i = 7: binary = 00000111 -> output I1, I2, I3 i = 8: binary = 00001000 -> output I4 i = 9: binary = 00001001 -> output I1, I4 i = 10: binary = 00001010 -> output I2, I4 i = 11: binary = 00001011 -> output I1, I2, I4 i = 12: binary = 00001100 -> output I3, I4 i = 13: binary = 00001011 -> output I1, I2, I4 i = 14: binary = 00001110 -> output I2, I2, I4 i = 15: binary = 00001111 -> output I1, I2, I3, I4