现有一张表A
id name1 name2
1 aa bb
2 aa cc
3 bb cc
4 aa dd
.................我需要统计每个名字出现的次数
希望得到的结果是
aa 3
bb 2
cc 2
dd 1请问要一句sql的话怎么写出来??
id name1 name2
1 aa bb
2 aa cc
3 bb cc
4 aa dd
.................我需要统计每个名字出现的次数
希望得到的结果是
aa 3
bb 2
cc 2
dd 1请问要一句sql的话怎么写出来??
+------+-------+-------+
| id | name1 | name2 |
+------+-------+-------+
| 1 | aa | bb |
| 2 | aa | cc |
| 3 | bb | cc |
| 4 | aa | dd |
+------+-------+-------+
4 rows in set (0.00 sec)root@localhost : test 04:49:22>select name,sum(num) from (select name1 name,count(*) num from dd group by name1 union all select name2 name,count(*) num from dd group by name2) t group by name ;
+------+----------+
| name | sum(num) |
+------+----------+
| aa | 3 |
| bb | 2 |
| cc | 2 |
| dd | 1 |
+------+----------+
4 rows in set (0.00 sec)
form (
select name1 from 现有一张表A
union all
select name2 from 现有一张表A
) t
group by name1
select name,count(name)
from(select name1 as name from A union all select name2 as name from A) as tmp
group by name order by name asc
实际上就是用union all把两列合成一列,再用count函数。
FROM (
SELECT name1 FROM A
UNION ALL
SELECT name2 FROM A
)
t
GROUP BY name1;
第一:select name1 name,count(*) num from dd group by name1
生成的表如下
name num
aa 3
bb 1
********************************
第二:select name2 name,count(*) num from dd group by name2
生成的表如下
name num
bb 1
cc 2
dd 1
**********************************
第三:select name1 name,count(*) num from dd group by name1
union all
select name2 name,count(*) num from dd group by name2
生成的表
name num
aa 3
bb 1
bb 1
cc 2
dd 1
*****************************************
最后在第三步的基础上再次搜索就可以得到结果
select name,sum(num) from (select name1 name,count(*) num from dd group by name1 union all select name2 name,count(*) num from dd group by name2) t group by name ;
再次楼主要注意 union all 和union的区别