添加连接后 MySQL 查询中断

Question

我有一个查询，用于在 CMS 中加载循环数据以显示帖子。投票栏中的一切都运行良好，直到我离开加入评论栏。评论显示正常，但总投票数、赞成票数和反对票数严重偏离。如果您需要查看表格，请告诉我。

SELECT
count(DISTINCT comment.comment ) AS Comment,
idea.dateofcreation AS timestamp, 
idea.userId AS userId,
idea.id AS ID,
idea.text AS Idea,
page.permalink AS Permalink,
user.name AS Username,
COUNT(CASE WHEN votelog.vote !="" THEN 1 END) AS 'totalvotes',
COUNT(CASE WHEN votelog.vote = '1' THEN 1 END) AS 'upvote',
COUNT(CASE WHEN votelog.vote = '-1' THEN 1 END) AS 'downvote'
FROM idea
LEFT JOIN votelog ON idea.id = votelog.ideaid
LEFT JOIN user ON idea.userId = user.id
LEFT JOIN page ON idea.id = page.ideaid
LEFT join comment ON comment.ideaid = idea.id 
GROUP BY idea.id
ORDER BY totalvotes DESC

Answer 1

您似乎决定加入评论表只是为了计算有多少条评论。

但是通过这样做，您现在会生成多行，每个评论一行，这会消除所有其他计数。

我建议使用标量子查询来计算评论，并删除连接。像这样的东西:

SELECT
(select count(DISTINCT comment) from comment c where c.ideaid = idea.id) AS Comment,
idea.dateofcreation AS timestamp, 
idea.userId AS userId,
idea.id AS ID,
idea.text AS Idea,
page.permalink AS Permalink,
user.name AS Username,
COUNT(CASE WHEN votelog.vote !="" THEN 1 END) AS 'totalvotes',
COUNT(CASE WHEN votelog.vote = '1' THEN 1 END) AS 'upvote',
COUNT(CASE WHEN votelog.vote = '-1' THEN 1 END) AS 'downvote'
FROM idea
LEFT JOIN votelog ON idea.id = votelog.ideaid
LEFT JOIN user ON idea.userId = user.id
LEFT JOIN page ON idea.id = page.ideaid
GROUP BY idea.id
ORDER BY totalvotes DESC