SQL查询显示列中的列项目？

Question

我有一个数据库表中象下面这样：

----------------------------- 
| ID | Item1 | Item2 | Item3| 
----------------------------- 
| 1 | A1 | B3 | C1 | 
| 2 | A2 | B3 | C2 | 
| 3 | A1 | B2 | C2 | 
| 4 | A2 | B1 | C1 | 
----------------------------- 

我想显示其计数表中的项目，如下面：

--------------- 
| item | count| 
--------------- 
| A1 | 2 | 
| A2 | 2 | 
| B1 | 1 | 
| B2 | 1 | 
| B3 | 2 | 
| C1 | 2 | 
| C2 | 2 | 
--------------- 

这可能吗？如果是，查询如何？到目前为止，我只管理一个项目的foreach这样来显示它一个：

------------------ 
| Item1 | Count | 
------------------ 
SELECT COUNT(*) AS `Count` , `Item1` FROM `table` GROUP BY `Item1`; 

------------------ 
| Item2 | Count | 
------------------ 
SELECT COUNT(*) AS `Count` , `Item2` FROM `table` GROUP BY `Item2`; 

------------------ 
| Item3 | Count | 
------------------ 
SELECT COUNT(*) AS `Count` , `Item3` FROM `table` GROUP BY `Item3`; 

Answer 1

使用union all和group by：

select item, count(*) 
from ((select item1 as item from `table`) union all 
     (select item2 as item from `table`) union all 
     (select item3 as item from `table`) 
    ) t 
group by item 
order by item;