mysql - 从多个行和列中选择唯一值作为单个列表值

Question

我有包含如下值的表格:

Column1 | Column2 | Column3 | Column4
=====================================
1       | 3       | 5       | NULL
1       | 3       | NULL    | NULL
1       | 4       | NULL    | NULL
2       | NULL    | NULL    | NULL

如果我想要结果只有唯一值而不是 null，则查询如何，如下所示:

1,2,3,4,5

Answer 1

只需 4 个查询，每列选择一个查询并将结果合并在一起:-

SELECT Column1 AS aCol FROM SomeTable WHERE Column1 IS NOT NULL
UNION
SELECT Column2 AS aCol FROM SomeTable WHERE Column2 IS NOT NULL
UNION
SELECT Column3 AS aCol FROM SomeTable WHERE Column3 IS NOT NULL
UNION
SELECT Column4 AS aCol FROM SomeTable WHERE Column4 IS NOT NULL

或者，如果您只想返回一个字段并将它们全部连接起来:-

SELECT GROUP_CONCAT(aCol)
FROM
(
    SELECT Column1 AS aCol FROM SomeTable 
    UNION
    SELECT Column2 AS aCol FROM SomeTable 
    UNION
    SELECT Column3 AS aCol FROM SomeTable 
    UNION
    SELECT Column4 AS aCol FROM SomeTable 
) Sub0