为什么此聚合查询返回的行比应有的少一行？
我有一个经典的“获取一个表中的所有行以及另一个表中相应行数”的问题，该问题应该通过此查询来解决：
SELECT
ideas.id,
ideas.idea,
submitted,
COUNT(votes.id) AS vote_count
FROM ideas
LEFT OUTER JOIN votes ON ideas.id = votes.idea
WHERE dead = 0
GROUP BY votes.idea
ORDER BY vote_count DESC, submitted DESC
LIMIT 10;
想法中有 4 行（dead = 0），投票中有一行（与第一个想法）。但是，此查询返回具有正确 vote_counts 的两条记录（idea #1 和 idea #2）。为什么这没有返回创意中的所有记录？
I have the classic 'get all rows in one table with number of corresponding rows in another table' issue which should be solved by this query:
SELECT
ideas.id,
ideas.idea,
submitted,
COUNT(votes.id) AS vote_count
FROM ideas
LEFT OUTER JOIN votes ON ideas.id = votes.idea
WHERE dead = 0
GROUP BY votes.idea
ORDER BY vote_count DESC, submitted DESC
LIMIT 10;
There are 4 rows (with dead = 0) in ideas and one row in votes (relating to the first idea). However this query returns two records (idea #1 and idea #2) with correct vote_counts. Why is this not returning all of the records in ideas?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
当您说
GROUP BY votes.idea时，您要求为投票中的每个想法值提供一个结果行。由于您说 votes 只有一行，因此您应该期望结果中只有两条记录 - 一条对应于该行投票中的想法值，另一条记录为 NULL（压缩没有匹配投票记录的三行）。您指的是
GROUP BY ideas.idea吗？When you say
GROUP BY votes.idea, you are asking for one result row per idea value in votes. Since you say votes has only one row, you should expect only two records in the result — one corresponding to the idea value in that row of votes, and the other with NULL (condensing the three rows with no matching vote record).Did you mean
GROUP BY ideas.idea?更改：
为：
因为
votes.idea可以为 NULL。Change:
to:
Because
votes.ideacan be NULL.