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]