从第3个表中看不到mySQL COUNT [英] Cannot see mySQL COUNT from 3rd table

问题描述

我发布了另一个这样的消息。但没有帮助。所以我做了一些更多的阅读，我有这个代码工作到一点：

编辑新版本&新问题

  SELECT i。*，o.organ_name，o.organ_logo，vtable。* 
从heroku_056eb661631f253.op_ideas我
 JOIN 
（SELECT 
 COUNT（v.agree）如同意，
 COUNT（v.disagree = 1或空）为不同意，
 COUNT（v.obstain = 1或NULL）作为弃权
 FROM op_idea_vote v 
）的虚函数表
 LEFT JOIN op_organs o在i.post_type = o.organs_id 
  

op_idea_vote表中只有1行，op_ideas表中只有3行。

编辑工作解决方案

>感谢WayneC，这里是工作代码：

  SELECT i。*，o.organ_name，o.organ_logo，vtable。 * 
从heroku_056eb661631f253.op_ideas我
 JOIN 
（SELECT v.idea_Id，
 COUNT（v.agree = 1或NULL）作为同意，
 COUNT（五。不同意= 1或空）为不同意，
 COUNT（v.obstain = 1或NULL）作为弃权
 FROM op_idea_vote v 
组BY v.idea_id 
）的虚函数表虚表ON .idea_id = i.idea_id 
 LEFT JOIN op_organs o ON i.post_type = o.organs_id 
 WHERE idea_geo ='International'; 
  

解决方案

尝试此

  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 
从op_idea_vote v 
组BY v.idea_id 
）的虚函数表ON vtable.idea_id = i.idea_id 
 LEFT JOIN op_organs o在i.post_type = o.organs_id 
  

下面的答案缺少组by和vtable的连接条件

I have posted another message like this. But none helped. So I have done some more reading and I have this code which works to a point:

EDIT NEW VERSION & NEW ISSUE

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_id

There is only 1 row in the op_idea_vote table, and 3 in the op_ideas table. But it's giving each row the total in each op_ideas row

EDIT WORKING SOLUTION

Thanks to WayneC, here is the working code:

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';

解决方案

Try this

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

The answer below was missing the group by, and join condition for vtable

这篇关于从第3个表中看不到mySQL COUNT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！