你可以尝试使用
GROUP_CONCAT
方法。它可以扩展到任意数量的列:
CREATE TABLE testdata(id INT PRIMARY KEY, field1 INT, field2 INT, field3 INT);
INSERT INTO testdata (id, field1, field2, field3) VALUES
(1, 7, 2, 9),
(2, 4, 7, 8),
(3, 6, 3, 2);
SELECT testdata.id, field1, field2, field3, GROUP_CONCAT(rtc.fields ORDER BY fields SEPARATOR '') AS fields_sorted
FROM testdata
INNER JOIN (
SELECT id, field1 AS fields FROM testdata UNION ALL
SELECT id, field2 AS fields FROM testdata UNION ALL
SELECT id, field3 AS fields FROM testdata
) AS rtc ON testdata.id = rtc.id
GROUP BY testdata.id, field1, field2, field3;
输出:
+
| id | field1 | field2 | field3 | fields_sorted |
+
| 1 | 7 | 2 | 9 | 279 |
| 2 | 4 | 7 | 8 | 478 |
| 3 | 6 | 3 | 2 | 236 |
+