基于一个元素的查询

Question

我得到了以下数据库模式

Cows (cowID, cowName, cowAge)
CowPurchaser (purchaserID, name, address)
PurchaseRecord (purchaserID, cowID, year)

对于每一年的奶牛购买者“农田”购买至少一头奶牛，找出购买的奶牛数量。输出是一组元组，它表示“农田”购买的奶牛的年份和数量

我不确定如何处理这一点。我试过了

SELECT (pr.year, COUNT(c.name))
FROM PurchaseRecord pr, CowPurchaser cp, Cows c
WHERE cp.name = "FARMLAND" AND COUNT(c.name) >= 1

这没有给出我想要的结果。我能做错什么呢？我怎么才能修复它？

Answer 1

SELECT pr.year, COUNT(c.name)
FROM PurchaseRecord pr
     INNER JOIN CowPurchaser cp ON pr.purchaserID=cp.purchaserID
     INNER JOIN Cows c ON pr.cowID=c.cowID
WHERE cp.name = "FARMLAND" 
GROUP BY pr.year
HAVING COUNT(c.name) >= 1