SQL:计算表中不存在的行 [英] SQL: Count rows which are not present in table

问题描述

我有两个表:EmployeeTable&TaskAssignmentTable.他们看起来像这样:
TaskAssignmentTable显示分配给员工的任务.为了向员工分配新任务，我希望将任务数分配给其他人，然后再将任务分配给分配最少任务的人.
问题:在TaskAssignmentTable上使用普通count()会在此表中产生结果:
但是我想要的是表之间的某种连接，它显示第一个表中存在的行数，而第二个表中不存在的行数，计数等于0，像这样:

I got two tables called: EmployeeTable & TaskAssignmentTable. They look like this :
TaskAssignmentTable shows tasks assigned to employees. In order to assign new tasks to employees i want to have count of tasks assigned to different people and then assign task to people who have least tasks assigned.
Problem: using normal count() on TaskAssignmentTable results in this table:
But what i want is some sort of join between tables which shows count of rows which are present in first table and absent in 2nd table with count equal to 0 like this one:

那么联接表并执行此操作的SQL查询将是什么?(可选:由于我使用的是C#Linq-2-SQL，如果有人可以为此编写LINQ语法，我将不胜感激.)

So what would be the SQL query to join tables and do such thing? (Optional: Since I'm using C# Linq-2-SQL i would be grateful if someone can write LINQ syntax for this).

推荐答案

您需要一个 LEFT OUTER JOIN ，它基于您希望第一个表中存在但第二个表中不存在的行的声明:

You need a LEFT OUTER JOIN based upon your statement that you want rows that are present in the first table but not the second:

SELECT EmployeeID, Name, Count(TaskID) as CNT
FROM EmployeeTable e
LEFT JOIN TaskAssignmentTable t 
    ON e.employeeID = t.FKEmployeeID
GROUP BY EmployeeID, Name

这篇关于SQL:计算表中不存在的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！