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 单片机 TC277 PWM
  • ¥15 在更新角色衣服索引后,Sprite 并未正确显示更新的效果该如何去解决orz(标签-c#)
  • ¥15 VAE代码如何画混淆矩阵
  • ¥15 求遗传算法GAMS代码
  • ¥15 雄安新区高光谱数据集的下载网址打不开
  • ¥66 android运行时native和graphics内存详细信息获取
  • ¥15 rk3566 Android11 USB摄像头 微信
  • ¥15 torch框架下的强化学习DQN训练奖励值浮动过低,希望指导如何调整
  • ¥35 西门子博图v16安装密钥提示CryptAcquireContext MS_DEF_PROV Error of containger opening
  • ¥15 mes系统扫码追溯功能
手机看
程序员都在用的中文IT技术交流社区

程序员都在用的中文IT技术交流社区

专业的中文 IT 技术社区,与千万技术人共成长

专业的中文 IT 技术社区,与千万技术人共成长

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

关注【CSDN】视频号,行业资讯、技术分享精彩不断,直播好礼送不停!

客服 返回
顶部