如何使用联接表并使用选择查询进行计数

Question

我不擅长复杂的sql查询，所以我发布这个问题，如果你能帮我，我会很高兴的。

我有两张桌子。第一个是idea_box，第二个是idea_box_voting。现在，我想要连接两个表，然后要计算其中id的总数为0和1。这是我的两张桌子。

idea_box
--------

Idea_id property_id     idea_title  the_idea    user_id
25      0               Idea 25     Lorem       23      
24      0               Idea 24     Lorem       23      
23      0               Idea 23     Lorem       23          
22      0               Idea 22     Lorem       23
21      0               Idea 21     Lorem       23
20      0               Idea 20     Lorem       23

idea_box_voting
---------------

vote_id     idea_id ip              user_id     thumbs
37          25      192.168.1.14    23          1
44          25      192.168.1.14    23          1
47          25      192.168.1.14    23          0
40          25      192.168.1.14    23          1
41          24      192.168.1.14    23          1
38          24      192.168.1.14    23          0
45          24      192.168.1.14    23          1
39          23      192.168.1.14    23          1
46          23      192.168.1.14    23          0
42          23      192.168.1.14    23          0
43          22      192.168.1.14    23          1

和我想要这个输出：

Idea_id property_id     idea_title  the_idea    user_id one_count zero_count
25      0               Idea 25     Lorem       23      3        1
24      0               Idea 24     Lorem       23      2        1
23      0               Idea 23     Lorem       23      1        2  
22      0               Idea 22     Lorem       23      1        0
21      0               Idea 21     Lorem       23      0        0
20      0               Idea 20     Lorem       23      0        0

谢谢。

Answer 1

SELECT ib.*,
    (
        SELECT COUNT(*)
        FROM idea_box_voting ibv
        WHERE ibv.idea_id = ib.idea_id AND ibv.thumbs = 1
    ) AS one_count,
    (
        SELECT COUNT(*)
        FROM idea_box_voting ibv
        WHERE ibv.idea_id = ib.idea_id AND ibv.thumbs = 0
    ) AS zero_count
FROM idea_box ib

Answer 2

尝尝这个

Select Count(CASE WHEN vote.thumbs = 0 THEN 1 END) AS Zero_Count
       Count(CASE WHEN vote.thumbs = 1 THEN 1 END) AS One_Count
FROM idea_box as ib
left Join idea_box_voting as vote
On vote.idea_id = ib.idea_id
Group by vote.idea_id

Answer 3

SELECT
      Idea_id
    , property_id
    , idea_title
    , the_idea
    , user_id
    , COUNT(CASE
            WHEN thumbs = 1 THEN thumbs END) one_count
    , COUNT(CASE
            WHEN thumbs = 0 THEN thumbs END) zero_count
FROM idea_box i
      LEFT JOIN idea_box_voting v
                  ON i.Idea_id = v.idea_id
GROUP BY
      Idea_id
    , property_id
    , idea_title
    , the_idea
    , user_id