mysql - SQL 查询查找两列之间不同值的计数

Question

给定下表，名为 demo:

+----+-------+--------+
| id | bag_1 | bag_2  |
+----+-------+--------+
|  1 | Alpha | Alpha  |
|  2 | Alpha | Beta   |
|  3 | Beta  | Beta   |
|  4 | Beta  | Omega  |
|  5 | Beta  | Delta  |
|  6 | Beta  | Sigma  |
|  7 | Gamma | Alpha  |
|  8 | Gamma | Lambda |
|  9 | Gamma | Omega  |
+----+-------+--------+

如何查找 bag_1 和 bag_2 列中不同值的计数？

(预期计数 = 7)

注意:原始表约有 180 万行，因此连接从未成功完成。

Answer 1

可以使用UNION ALL来收集所有可能的值，然后COUNT(DISTINCT bag)将完​​成剩下的工作，如下所示:


SELECT COUNT(DISTINCT bag) from (SELECT bag_1 AS bag FROM demo UNION ALL SELECT bag_2 AS bag FROM demo) a;



+--------------------+
|计数(不同的包)|
+--------------------+
| 7 |
+--------------------+


编辑:向@Tim Biegeleisen 和@eggyal 行屈膝礼，较短的形式是:


SELECT COUNT(*) from (SELECT bag_1 AS bag FROM demo UNION SELECT bag_2 AS bag FROM demo) a;