MySQL合并计数多列 [英] Mysql merge count multiple column
本文介绍了MySQL合并计数多列的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个像这样的表(tb_data)
I have a table(tb_data) which like this
+---------+---------------------+---------------------+---------------------+---------------------+
| Disease | Additional_Disease1 | Additional_Disease2 | Additional_Disease3 | Additional_Disease4 |
+---------+---------------------+---------------------+---------------------+---------------------+
| A01 | A03 | A03 | | |
| A03 | A02 | | | |
| A03 | A05 | | | |
| A03 | A05 | | | |
| A02 | A05 | A01 | A03 | |
+---------+---------------------+---------------------+---------------------+---------------------+
我的问题是如何使它像这样
My question is how to make it like this
+---------+-------+
| Disease | Total |
+---------+-------+
| A03 | 6 |
| A05 | 3 |
| A01 | 2 |
| A02 | 2 |
+---------+-------+
哦,这是我失败的尝试
select Disease,
count(Disease + Additional_Disease1 + Additional_Disease2 + Additional_Disease3 + Additional Disease_4) as Total
from tb_data
group by Disease
order by Disease desc
我也尝试过这,但是没有成功,它说未知字段列表"中的疾病"列,因为我真的不明白这是怎么回事
I've also tried this but it didn't work, it says "Unknown column 'Disease' in 'field list' as i really don't understand what was wrong
推荐答案
您可以使用全部联合
取消数据集的透视,然后进行聚合:
You can use union all
to unpivot your dataset, and then aggregation:
select disease, count(*) total
from (
select disease from mytable
union all select additional_disease1 from mytable
union all select additional_disease2 from mytable
union all select additional_disease3 from mytable
union all select additional_disease4 from mytable
) t
group by disease
order by total desc, disease
这篇关于MySQL合并计数多列的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文