I'm trying to write a simple SQL query to show all possible combinations of data in a single table. Here's the table:

I've only got as fair as pairing all the data using CROSS JOIN: "apple,orange", "apple,pear" etc.

SELECT t1.fruit, t2.fruit
FROM fruits t1
CROSS JOIN fruits t2
WHERE t1.fruit < t2.fruit

Instead I'm looking for all unique combinations in alphabetical order, e.g.

• apple
• apple,orange
• apple,orange,pear
• apple,orange,pear,plum
• apple,pear
• apple,plum
• apple,orange,plum
• apple,pear,plum
• orange
• orange,pear
• orange,pear,plum
• orange,plum
• pear
• pear,plum
• plum

i.e. as long as a combination exists once, it doesn't need to appear again in a different order, e.g. with apple,orange, there is no need for orange,apple

CodePudding user response：

Below query should work:

WITH RECURSIVE cte AS (
    SELECT A.id,
        CONCAT(A.fruit,',',GROUP_CONCAT(B.fruit ORDER BY B.id)) AS combinations,
        COUNT(*) AS count_of_delims 
    FROM fruits A 
    INNER JOIN fruits B 
    ON A.id<B.id 
    GROUP BY A.id,A.fruit
  
    UNION ALL
  
    SELECT id,
        SUBSTRING_INDEX(combinations,',',count_of_delims),
        count_of_delims-1 
    FROM cte 
    WHERE count_of_delims>0
)
SELECT combinations FROM cte ORDER BY id;

Here is a working example in DB Fiddle.

CodePudding user response：

Here is a very ugly solution that works (try here):

SELECT
    CONCAT(T.t1,
        CONCAT(IF(T.t2 IS NULL, '',
            CONCAT(', ', T.t2)) ,
            CONCAT(IF(T.t3 IS NULL, '',
                CONCAT(', ', T.t3)) , IF(T.t4 IS NULL, '',
                CONCAT(', ', T.t4))
            )
        )
    )
              
'result' FROM

(

    SELECT T1.fruit 't1', TX.* FROM fruits T1
    LEFT JOIN (

        SELECT T2.fruit 't2', TY.* FROM fruits T2
        LEFT JOIN (

                  SELECT T3.fruit 't3', t4 't4' FROM fruits T3
                  LEFT JOIN (

                    SELECT T4.fruit 't4' FROM fruits T4 WHERE id = 4

                  ) TZ
                    ON T3.fruit < t4
                  WHERE T3.fruit > 'orange'
                  UNION ALL
                  SELECT fruit, null FROM fruits WHERE id = 3
          
        ) TY
          ON T2.fruit < t3
        WHERE T2.fruit > 'apple'
        UNION ALL
        SELECT fruit, null, null FROM fruits WHERE id IN (3, 2)

    ) TX
      ON T1.fruit < t2
    UNION ALL
    SELECT fruit, null, null, null FROM fruits WHERE id IN (3, 2, 1)

) T

ORDER BY t1, t2, t3, t4

Result:

 --------------------------- 
|          result           |
 --------------------------- 
| apple                     |
| apple, orange             |
| apple, orange, pear       |
| apple, orange, pear, plum |
| apple, orange, plum       |
| apple, pear               |
| apple, pear, plum         |
| apple, plum               |
| orange                    |
| orange, pear              |
| orange, pear, plum        |
| orange, plum              |
| pear                      |
| pear, plum                |
| plum                      |
 ---------------------------