SQL 对多个表进行计数和连接

Question

有一个产品表，其中包含所有产品详细信息。一个产品可以被提名到热门表中，不同的人可以对其进行投票。我只需要计算注册用户的投票。目前，它正在计算特定产品的所有投票。我该如何克服这个问题？我已经将我的sql代码粘贴在下面：

SELECT popular.name, popular.product_id, product.text, product.author, count(        vote.product_id ) AS votes
FROM popular
LEFT JOIN product ON ( product.id = popular.product_id )
LEFT JOIN vote ON ( vote.product_id = popular.product_id )
where popular.hidden = '1' and
popular.name in (select registered.name from registered 
where registered.course_id='".$course_id."' and 
registered.section = '".$section."' and 
registered.term = '".$term."')
GROUP BY popular.product_id
ORDER BY votes DESC Limit 10

Answer 1

您缺少投票和其他表之间的连接

Select P.ID, P.text, count(v.Product_ID) as votes
FROM Product PRO
LEFT JOIN Popular Pop
  ON PRO.ID = Pop.Product_ID
LEFT JOIN VOTE V   --<-- This is missing
  on V.Product_ID = Pro.ID
Group by P.ID, Pro.Text
order by votes Desc

Answer 2

SELECT pop.product_id, pd.text, count( vt.product_id ) AS votes
FROM popular pop
LEFT JOIN product pd ON(pd.product = pop.product_id)
LEFT JOIN vote vt ON(vt.product_id = pop.product_id)
WHERE 1 = 1 // or pop.product_id = yourProductId here
GROUP BY pop.product.id
ORDER BY vt.votes DESC

Answer 3

SELECT popular.product_id, product.text, count( vote.product_id ) AS votes
FROM popular, product, vote
WHERE product.id = popular.product_id AND product.id = vote.product_id
GROUP BY product.id
ORDER BY votes DESC

请注意，此查询不会返回没有投票的产品。如果您的数据库中有此类记录，您应该LEFT JOIN与vote表。