对比两张表中的数据
0
最佳答案
2
这里涉及到几种情况
1.如果2个表的产品都是一样的只是数量不一样
select f1.foodName,f1.foodCount--,f2.foodName,f2.foodCount from food1 f1 left join food2 f2 on f1.foodName=f2.foodName where f1.foodCount != f2.foodCount
2.如果2个表以表food1为主表，那么2表的产品一定在1表汇总存在
select f1.foodName,f1.foodCount--,f2.foodName,f2.foodCount from food1 f1 left join food2 f2 on f1.foodName=f2.foodName where (f1.foodCount is null or f2.foodCount is null ) or f1.foodCount != f2.foodCount
3.如果2个表没有主次之分，1表或者2表都有可能有自己独特的产品
select f1.foodName,f1.foodCount--,f2.foodName,f2.foodCount from food1 f1 left join food2 f2 on f1.foodName=f2.foodName where (f1.foodCount is null or f2.foodCount is null ) or f1.foodCount != f2.foodCount union select f1.foodName,f1.foodCount--,f2.foodName,f2.foodCount from food2 f2 left join food1 f1 on f1.foodName=f2.foodName where (f1.foodCount is null or f2.foodCount is null ) or f1.foodCount != f2.foodCount
收获园豆：5
其他回答(3)
0
select * from food1 f1 where not exists( select 1 from food2 f2 where f1.foodName=f2.foodName and f1.foodCount=f2.foodCount)
随便一写，没验证，不知道有没有问题
0
select * from food1 where foodName not in (select foodName from food1 f1,food2 f2 where f1.foodName = f2.foodName and f1.foodCount = f2.foodCount )
思路：子查询筛选出两个表的相同记录的foodName，主查询筛选foodName不在子查询集合里的所有记录。
0
使用关键字except就行了
