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]
go
create table [tableA]([Id] int,[food] varchar(4))
insert [tableA]
select 1,'苹果' union all
select 2,'苹果' union all
select 3,'西瓜'
--> 测试数据：[tableC]
if object_id('[tableC]') is not null drop table [tableC]
go
create table [tableC]([foodId] int,[foodName] varchar(4))
insert [tableC]
select 1,'苹果' union all
select 2,'西瓜' union all
select 3,'香蕉'


select 
    food=C.foodName,
    数量=COUNT(A.food) from [tableA] A right join [tableC] C
on A.food=C.foodName
group by C.foodName

/*
苹果    2
西瓜    1
香蕉    0
*/

drop table [tableA]
drop table [tableC]