我有一个可以包含重复描述的“update”表,我想返回重复的描述和它们的计数,所以我创建了这个查询:
SELECT description, count(description) AS count
FROM updates INNER JOIN participations ON participations.update_id = updates.id
INNER JOIN customer ON customer.id = participations.customer_id
INNER JOIN garages ON garages.id = customer.garage_id
WHERE (updates.created_at >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH))
AND garages.`id` = 1
GROUP BY description
ORDER BY count desc
LIMIT 10返回的计数不是我所期望的。我相信原因是因为许多客户可以共享一个更新,所以我得到了副本,因为表中的实际副本,以及因为相同的更新记录被多次返回。如何修复查询,使其仅计算更新表中实际重复的描述字段。谢谢
发布于 2012-02-29 17:08:04
您可能会重写查询以使用EXISTS,因为您需要客户才能到达车库:-)
SELECT description, count(description) AS count
FROM updates
WHERE (updates.created_at >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH))
AND EXISTS (select null from participations INNER JOIN customer ON customer.id = participations.customer_id WHERE participations.update_id = updates.id AND customer.garage_id = 1)
GROUP BY description
ORDER BY count desc
LIMIT 10https://stackoverflow.com/questions/9495804
复制相似问题