- MySQL：多字段复合统计
-
菜鸟求助：eves表中有三个国家字段，nat1, nat2, nat3，nat1为非空，其它两个不一定有数据。现在想统计3列中每个国家（包括出现在nat2和nat3里的国家）出现的次数，请问如何SELECT？感谢！！试过COUNT和GROUP BY，似乎无法实现啊……
select a.nat, count(1) as num from (
(select nat1 as nat from Test where nat1 is not null)
union all
(SELECT nat2 AS nat FROM Test WHERE nat2 IS NOT NULL)
UNION ALL
(SELECT nat3 AS nat FROM Test WHERE nat3 IS NOT NULL)
) as a group by a.nat
-
- caitodieresis 太牛了！虽然还不太懂原理，但抄试了一下，果然管用！谢谢！！！我得好好研究一下！
- 4 年多之前 回复
感谢 @晓呆同学 出手相助，菜鸟感激不尽！交下作业：
SELECT a.nat AS 国家, COUNT(1) AS 计数 FROM (
-> (SELECT nat1 AS nat FROM eves WHERE nat1 IS NOT NULL) UNION ALL
-> (SELECT nat2 AS nat FROM eves WHERE nat2 IS NOT NULL) UNION ALL
-> (SELECT nat3 AS nat FROM eves WHERE nat3 IS NOT NULL)
-> ) AS a GROUP BY a.nat ORDER BY CONVERT(a.nat USING gbk);
完美解决问题啊！
就是有点长，不知道有没有更简便一点的语法可用……
-
- caitodieresis 你是说分别统计出来，再手动加一下吗？如果能汇合在一个表视图里，看着不仅直观，导出数据也方便啊……
- 4 年多之前 回复
