douzhi6365 2015-12-25 20:46
浏览 54
已采纳

MySQL JOIN with where子句和group by count

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= 37 and 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).

  • 写回答

1条回答 默认 最新

  • duanjia7912 2015-12-25 20:59
    关注

    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
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
编辑
预览

报告相同问题?

悬赏问题

  • ¥15 关于#c##的问题:treenode反序列化后获取不到上一节点和下一节点,Fullpath和Handle报错
  • ¥15 一部手机能否同时用不同的app进入不同的直播间?
  • ¥15 没输出运行不了什么问题
  • ¥20 输入import torch显示Intel MKL FATAL ERROR,系统驱动1%,: Cannot load mkl_intel_thread.dll.
  • ¥15 点云密度大则包围盒小
  • ¥15 nginx使用nfs进行服务器的数据共享
  • ¥15 C#i编程中so-ir-192编码的字符集转码UTF8问题
  • ¥15 51嵌入式入门按键小项目
  • ¥30 海外项目,如何降低Google Map接口费用?
  • ¥15 fluentmeshing