使用总和，计数，分组依据和多个左联接MySQL执行查询

debugcn 发表于 Dev

拉胡尔·帕特尔（Rahul Patel）

我有三个表1.user，2.project_questions，3.votings。

id project_id user_id question
 1        593     308 Hello Question?
 2        593     308 This is Another Question?
 3        593     308 sdssds
 4        593     308 asdsd
 5        593     308 dsfdsfsdfsfsdfs
 6        593     308 nro
 7        593     308 dsfsdfsdfsdf
 8        593     308 zxzx
 9        593     308 zxzxasasasasasasasas
10        593     308 zxzxasasasasasasasasfdsfdsfdsfsdfdsfsdf
11        593     308 fdfdsf
12        593     308 saddsadsad
13        593     308 ghvhgvhg

这是我的查询。

SELECT `project_questions`.*, count(votings.vote) as vote, 
 sum(case when votings.yes=1 then 1 else 0 end) as yes, 
 sum(case when votings.no=1 then 1 else 0 end) as no, 
 `user`.`image`, `user`.`user_name`, `user`.`last_name` FROM
(`project_questions`) LEFT JOIN `votings` ON  
`votings`.`question_id`=`project_questions`.`id` LEFT JOIN `user` ON 
`user`.`user_id`=`project_questions`.`user_id` WHERE
`project_questions`.`project_id` = '593' GROUP BY `votings`.`question_id`

投票表！[] [2]

用户表具有三个字段，如user_id，first_name，username。预期成绩。

问题是是，没有票数和总票数。前任。

这是我的结果

但有时某些问题未显示。

特伦特·劳埃德（Trent Lloyd）

评论中确认的答案是将project_questions.id分组，而不是投票。

SELECT
  project_questions.*,
  count(votings.vote) as vote,
  sum(case when votings.yes=1 then 1 else 0 end) as yes,
  sum(case when votings.no=1 then 1 else 0 end) as no,
  user.first_name,
  user.last_name
FROM
  project_questions
  LEFT JOIN votings ON votings.question_id=project_questions.id
  LEFT JOIN user ON user.user_id=project_questions.user_id
WHERE project_questions.project_id = '593'
GROUP BY project_questions.id

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。