MySQL:多字段复合统计

菜鸟求助:eves表中有三个国家字段,nat1, nat2, nat3,nat1为非空,其它两个不一定有数据。现在想统计3列中每个国家(包括出现在nat2和nat3里的国家)出现的次数,请问如何SELECT?感谢!!试过COUNT和GROUP BY,似乎无法实现啊……

4个回答

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
caitodieresis 太牛了!虽然还不太懂原理,但抄试了一下,果然管用!谢谢!!!我得好好研究一下!
4 年多之前 回复

把表明换一下,我这表明是Test~~~,换成eves 就好了

感谢 @晓呆同学 出手相助,菜鸟感激不尽!交下作业:

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
caitodieresis 你是说分别统计出来,再手动加一下吗?如果能汇合在一个表视图里,看着不仅直观,导出数据也方便啊……
4 年多之前 回复
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问