Mysql计数如果没有匹配则返回零 [英] Mysql Count to return zero if no match

问题描述

我有一组约9000个教师ids在一个数组，我把它们放在一个字符串，如：

I have a set of approx 9000 tutor ids in an array and i have put them in a string like:

(1,2, 3, 4,5,6,7,....9000,9001,9002)

以便我可以在以下查询中使用它们：

so that i can use them in the following query:

select count(student_assignment.assignment_id) as total_assignment from 
student_assignment, assigned_tutor_fk where  assignment_status = 'closed'
 and assigned_tutor_fk in (1,2, 3, 4,5,6,7,..100,101,103...9000,9001,9002)
 group by assigned tutor_fk.

我想计算与每个导师相关联的总行数（assigned_tutor_fk）没有一个赋值，即那些没有赋值
记录在表中我想显示他们的分配计数为0，我只想要我的查询返回计数和assigned_tutor_fk
我的表结构是： / p>

I want to calculate total number of rows associated with each tutor(assigned_tutor_fk), and those tutors which do not have an assignment ie those which do not have assignment record in the table i want to show their assignment count as 0, and i just want my query to return count and assigned_tutor_fk my table structure is:

    assignment_id | assigned_tutor_fk | assignment_date | student_id |
    |    1        |   2               |  22-01-2011     |  4         |
    |    2        |   3               |  14-03-2011     |  5         |

我想让我的输出像这样：

Im trying to get my output to be like this:

    |total_assignment | assigned_tutor_fk |
    |      5          | 4                 |
    |      2          | 7                 |
    |      0          | 8                 |

更新：我tthink我不能正确表达自己，我已经有一个过滤在另一个标准，这是非常复杂，组合这两个查询，所以现在我有一组教师的ID，我想要总和显示为零，如果教师没有任务记录。请帮助我，因为我不知道现在做什么

Update: I tthink i have not been able to express myself properly,i already have a list of tutors filtered on another criteria, it was very complex to combine these two queries so now i have a set of the tutor id's and i want the sum to be displayed as zero in case the tutors does not have assignment record. please help me on this as i don know wht to do now

推荐答案

SELECT  t.id, COUNT(sa.assignment_id)
FROM    tutor t
LEFT JOIN
        student_assignement sa
ON      sa.assignment_tutor_fk = t.id
WHERE   t.id IN (1, 2, ..., 9002)
GROUP BY
        t.id

这篇关于Mysql计数如果没有匹配则返回零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！