MYSQL获取轮询结果表

Question

我有 2 个表，一个有投票答案，一个有投票:

DESC polls;

Field   Type      Null  Key Default Extra
id      int(11)   NO    PRI NULL    auto_increment
pollId  int(11)   NO    MUL NULL    
answer  varchar(150)    YES NULL    

DESC votes;
Field    Type      Null Key Default Extra
id       int(11)    NO  PRI NULL    auto_increment
pollId   int(11)    NO  MUL NULL    
answerId int(11)    NO  MUL NULL    
userId   int(11)    NO  MUL NULL    

我正在尝试通过所有答案和投票获得以下结果:

pollId answerId numberOfVotes
1        1          20
1        2          10
1        3          0

我尝试将选票正确连接到答案，但它不起作用:

SELECT answers.id, COUNT(votes.answerId) FROM answers JOIN votes ON votes.pollId = answers.pollId GROUP BY votes.pollId;

Answer 1

使用左连接

DEMO

SELECT polls.pollId,polls.id,COUNT(userid) as counts
FROM polls left JOIN votes ON votes.pollId = polls.pollId and 
polls.id=votes.answerId
GROUP BY polls.pollId,polls.id order by polls.id

输出:

pollId  id  counts
14      17  0
14      18  2
14      19  0
14      20  0