DBMS: MySql
Problem: List all the possibilities of combinations between the numbers "1,2,3,4", so that they do not repeat between them.
Example:
1
2
3
4
12
13
14
21
23
24
31
32
34
41
42
43...
Combination of 4 numbers in MySQL
6
asked by anonymous 22.04.2014 / 21:02
1 answer
8
It's kind of strange to have to do this in SQL, but having a table with the digits, just do a CROSS JOIN with itself:
SELECT CONCAT(d1.digito, d2.digito)
FROM digitos d1
CROSS JOIN digitos d2
WHERE d1.digito <> d2.digito
This works for two-digit combinations. With one to four digits is more complicated, and SQL is not the right tool to solve this ... Even so, here goes, considering that the digit table contains NULL , 1 , 2 , 3 and 4 :
SELECT CONCAT(d1.digito, IFNULL(d2.digito,''), IFNULL(d3.digito,''), IFNULL(d4.digito,''))
FROM digitos d1
LEFT JOIN digitos d2
ON d1.digito <> d2.digito
OR d2.digito IS NULL
LEFT JOIN digitos d3
ON (d2.digito <> d3.digito AND d1.digito <> d3.digito)
OR d3.digito IS NULL
LEFT JOIN digitos d4
ON (d1.digito <> d4.digito AND d2.digito <> d4.digito AND d3.digito <> d4.digito)
OR d4.digito IS NULL
WHERE d1.digito IS NOT NULL
ORDER BY 0+CONCAT(d1.digito, IFNULL(d2.digito,''), IFNULL(d3.digito,''), IFNULL(d4.digito,''))
22.04.2014 / 21:23