mysql - 从具有包含相似数据的多列的表中选择唯一值

Question

我试图从一个表中选择不同国家代码的列表，该表有 3 个不同的列，其中包含国家代码数据。

示例

  Raw                                         Desired

+-----------+-----------+-----------+       +-----------+
| country_1 | country_2 | country_3 |       | countries |
+-----------+-----------+-----------+       +-----------+
| GBR       | GBR       | IRL       |       | ARG       |
| DEU       | FRA       | CHE       |       | AUS       |
| CHN       | CHN       | IND       |       | BRA       |
| USA       | CAN       | MEX       |       | CAN       |
| AUS       | JAP       | KOR       |       | CHE       |
| BRA       | ARG       | ARG       |       | CHN       |
| NOR       | SWE       | FIN       |       | DEU       |
| GBR       | FRA       | GBR       |       | FIN       |
| IRL       | IRL       | IRL       |       | FRA       |
+-----------+-----------+-----------+       | GBR       |
                                            | IND       |
                                            | IRL       |
                                            | JAP       |
                                            | KOR       |
                                            | MEX       |
                                            | NOR       |
                                            | SWE       |
                                            | USA       |
                                            +-----------+

到目前为止我最接近的查询是这样的:

mysql> SELECT
        CONCAT(
            (SELECT GROUP_CONCAT(DISTINCT country_1 ORDER BY country_1) FROM tbl),
            ',',
            (SELECT GROUP_CONCAT(DISTINCT country_2 ORDER BY country_2) FROM tbl),
            ',',
            (SELECT GROUP_CONCAT(DISTINCT country_3 ORDER BY country_3) FROM tbl)
        ) AS countries;

+-------------------------------------------------------------------------------------------------+  
| countries                                                                                       |  
+-------------------------------------------------------------------------------------------------+  
| AUS,BRA,CHN,DEU,GBR,IRL,NOR,USA,ARG,CAN,CHN,FRA,GBR,IRL,JAP,SWE,ARG,CHE,FIN,GBR,IND,IRL,KOR,MEX |  
+-------------------------------------------------------------------------------------------------+  

但是如您所见，仍然存在重复项，我也不确定如何将它们放入行中。

感谢任何帮助!

Answer 1

我不确定您为什么要使用组连接。使用 union 可以轻松实现所需的结果，它会自动删除重复项。

SELECT country1 AS countries FROM tab
UNION
SELECT country2 FROM tab
UNION
SELECT country3 FROM tab
ORDER BY 1;