无法从第三表中看到mySQL计数
无法从第三表中看到mySQL计数
提问于 2012-10-13 15:41:57
我又发了一条这样的消息。但没人帮上忙。因此，我做了一些更多的阅读，我有这样的代码，在某种程度上是可行的：
编辑新版本和新一期
SELECT i.*, o.organ_name, o.organ_logo, vtable.*
FROM heroku_056eb661631f253.op_ideas i
JOIN
(SELECT
COUNT(v.agree) as agree,
COUNT(v.disagree = 1 or null) as disagree,
COUNT(v.obstain = 1 or null) as abstain
FROM op_idea_vote v
) AS vtable
LEFT JOIN op_organs o ON i.post_type = o.organs_idop_idea_vote表中只有1行，op_ideas表中只有3行。但是它给出了每一行op_ideas行中的总数
编辑工作解决方案
感谢WayneC，下面是工作代码：
SELECT i.*, o.organ_name, o.organ_logo, vtable.*
FROM heroku_056eb661631f253.op_ideas i
JOIN
(SELECT v.idea_Id,
COUNT(v.agree = 1 or null) as agree,
COUNT(v.disagree = 1 or null) as disagree,
COUNT(v.obstain = 1 or null) as abstain
FROM op_idea_vote v
GROUP BY v.idea_id
) AS vtable ON vtable.idea_id = i.idea_id
LEFT JOIN op_organs o ON i.post_type = o.organs_id
WHERE idea_geo = 'International';回答 2
Stack Overflow用户
回答已采纳
发布于 2012-10-14 11:14:12
尝尝这个
SELECT i.*, o.organ_name, o.organ_logo, vtable.*
FROM heroku_056eb661631f253.op_ideas i
JOIN
(SELECT
COUNT(v.agree) as agree,
COUNT(v.disagree = 1 or null) as disagree,
COUNT(v.obstain = 1 or null) as abstain
FROM op_idea_vote v
GROUP BY v.idea_id
) AS vtable ON vtable.idea_id = i.idea_id
LEFT JOIN op_organs o ON i.post_type = o.organs_id下面的答案遗漏了组by，以及vtable的联接条件。
Stack Overflow用户
发布于 2012-10-13 16:03:56
这是你最初的查询
SELECT i.*, o.organ_name, o.organ_logo
FROM heroku_056eb661631f253.op_ideas i
JOIN
(SELECT
COUNT(v.agree) as agree,
COUNT(v.disagree = 1 or null) as disagree,
COUNT(v.obstain = 1 or null) as abstain
FROM op_idea_vote v, op_ideas i
WHERE v.idea_id = i.idea_id
) AS vtable
LEFT JOIN op_organs o ON i.post_type = o.organs_idvtable是一个伪装成表的子查询--在获取要与查询结果一起返回的字段方面，它与加入普通表没有什么不同。您必须在主查询中选择要返回的字段，否则不会返回。
SELECT i.*, o.organ_name, o.organ_logo, vtable.*
FROM heroku_056eb661631f253.op_ideas i
JOIN
(SELECT
COUNT(v.agree) as agree,
COUNT(v.disagree = 1 or null) as disagree,
COUNT(v.obstain = 1 or null) as abstain
FROM op_idea_vote v, op_ideas i
WHERE v.idea_id = i.idea_id
) AS vtable
LEFT JOIN op_organs o ON i.post_type = o.organs_id页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/12874647
复制相关文章
相似问题
腾讯云开发者
