问一个关于统计的有关问题

问一个关于统计的问题
tableA
Id food
1 苹果
2 苹果
3 西瓜





tableC
foodId foodName
1 苹果
2 西瓜
3 香蕉

求一条sql语句，能统计出这样一个效果

food 数量
苹果 2
西瓜 1
香蕉 0

[解决办法]

SQL code

select t2.foodName as food,count(t1.food) as 数量 from tableA t1,tableC t2  group by t2.foodName;
[解决办法]
SQL code
select t2.foodName as food,count(t1.food) as 数量 from tableA t1,tableC t2 where t1.food=t2.foodName group by t2.foodName;
[解决办法]
SQL code
--> 测试数据：[tableA]if object_id('[tableA]') is not null drop table [tableA]gocreate table [tableA]([Id] int,[food] varchar(4))insert [tableA]select 1,'苹果' union allselect 2,'苹果' union allselect 3,'西瓜'--> 测试数据：[tableC]if object_id('[tableC]') is not null drop table [tableC]gocreate table [tableC]([foodId] int,[foodName] varchar(4))insert [tableC]select 1,'苹果' union allselect 2,'西瓜' union allselect 3,'香蕉'select     food=C.foodName,    数量=COUNT(A.food) from [tableA] A right join [tableC] Con A.food=C.foodNamegroup by C.foodName/*苹果    2西瓜    1香蕉    0*/drop table [tableA]drop table [tableC]