求一条SQL语句

[luoryan]

现有一张表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的话怎么写出来？？

[小小小小周]

root@localhost : test 04:49:08>select * from dd;
+------+-------+-------+
| 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)

[ACMAIN_CHM]

select name1,count(*)
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

[xfy8xfy8]

[Quote=引用 2 楼 的回复:]
select name1,count(*)
form (
select name1 from 现有一张表A
union all
select name2 from 现有一张表A
) t
group by name1
[/Quote]
上面第2行应是from而不是form吧。俺们新手可是弄错一个字母都是要看半天的。
实际上就是用union all把两列合成一列，再用count函数。

[Rotel-刘志东]

SELECT  name1,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的区别