MySQL加入where子句和group by count

问题描述

我运行以下查询，

  SELECT status.status_id，status.status_name，COUNT（workbin.status_id）
 FROM`status` 
 LEFT JOIN`workbin` ON workbin.status_id = status.status_id 
 GROUP BY status.status_id 
  

并得到输出

  status_id status_name count 
 1新增44 
 2搁置1 
 3分析2 
 4开发中12 
 5测试中17 
 6在版本2中
 7已完成151 
 8 In Review 0 
 9单元测试0 
 11返工0 
 12重新打开0 
  

现在我需要在此查询中添加一个where子句，以基于 workbin 表中的用户标识来检索数据。所以结果只会显示用户的计数。用户ID也存储在 workbin 表中。

如果我添加where子句 WHERE workbin 。 task_assigned_id = 37
并找到很少的status_id和status_name缺少（具有0值）。
但是我需要所有分配给用户的状态计数（workbin.task_assigned_id）。

WHERE 子句中，您指定仅显示 task_assigned_id = 37 的记录> workbin 表。所以，你不能得到行，你在 workbin 表中没有一行（即使它在 status LEFT JOIN WHERE 子句的条件>部分：

  SELECT status.status_id，status.status_name，COUNT（workbin.status_id）
 FROM`status` 
 LEFT JOIN`workbin` on workbin.status_id = status.status_id AND workbin.task_assigned_id = 37 
 GROUP BY status.status_id 
  

I run a the following query,

SELECT status.status_id, status.status_name, COUNT(workbin.status_id)
FROM `status`
LEFT JOIN `workbin` ON workbin.status_id = status.status_id
GROUP BY status.status_id

and got the output

status_id status_name count
1   New              44
2   On Hold          1
3   In Analysis      2
4   In Development   12
5   In Testing       17
6   In Release       2
7   Completed        151
8   In Review        0
9   Unit Testing     0
11  Rework           0
12  Reopen           0

Now i need to add a where clause in this query, to retrive the data based on the user id in the workbin table. So the result will show only the count for the user. The user id is also stored in the workbin table.

If i add a where clause WHEREworkbin.task_assigned_id= 37and found few status_id and status_name are missing(which have the 0 value).But i need all the status counts for the task assigned user(workbin.task_assigned_id).

解决方案

It's because by WHERE clause, you are specifying to show only the records which have a value task_assigned_id = 37 in workbin table. So, you cannot get the rows, for which you don't have a row in workbin table (even though it is in status table.) The solution can be something like this, to keep the conditions of your WHERE clause in LEFT JOIN part:

SELECT status.status_id, status.status_name, COUNT(workbin.status_id)
FROM `status`
LEFT JOIN `workbin` ON workbin.status_id = status.status_id AND workbin.task_assigned_id= 37
GROUP BY status.status_id

这篇关于MySQL加入where子句和group by count的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！