用Count联接3个表 [英] Join 3 tables with Count

问题描述

我正在尝试编写一个简单的查询，该查询将显示所有项目及其团队成员总数，并按项目按字母顺序排序.如果一个项目没有分配团队成员，则该项目仍应包含在输出中.

I am trying to write a simple query that will display all projects and their total number of team members, sorted alphabetically by project. If a project does not have assigned team members, that project should still be included in the output.

CREATE TABLE Project ( ID INT IDENTITY(1,1), ProjectName VARCHAR(50), DueDate 
DATE)
CREATE TABLE Employee ( ID INT IDENTITY(1,1), EmployeeName VARCHAR(50) )
CREATE TABLE ProjectAssignment ( ID INT IDENTITY(1,1), ProjectID INT, 
EmployeeID INT)

INSERT INTO Project VALUES ('Alpha', '1/1/2040'), ('Bravo', '3/1/2030'), 
('Charlie', '2/1/2017'), ('Delta', '4/1/2017')
INSERT INTO Employee VALUES ('John'), ('Beth'), ('Tom'), ('Kim'), ('Jack')
INSERT INTO ProjectAssignment VALUES   (1, 1), (1, 2), (2, 2), (2, 3), (3, 
3), (3, 4), (1, 3)

--TABLE Project:
ID  ProjectName DueDate
1   Alpha   2040-01-01
2   Bravo   2030-03-01
3   Charlie 2017-02-01
4   Delta   2017-04-01

--TABLE Employee:
ID  EmployeeName 
1   John
2   Beth
3   Tom
4   Kim
5   Jack

--TABLE ProjectAssignment:
ID  ProjectID   EmployeeID
1   1   1
2   1   2
3   2   2
4   2   3
5   3   3
6   3   4
7   1   3

这是我的错误查询:

SELECT n.ProjectName, Count(t.ProjectID) as NumMembers
FROM Project p 
LEFT JOIN ProjectAssignment t ON p.EmployeeID = t.EmployeeID
LEFT JOIN employee e ON e.ProjectID = t.ProjectID
GROUP BY n.Project
ORDER BY n.Project

所需结果:

| ProjectName | NumMembers  |
+-------------+-------------+
| Alpha       | 3           |
| Bravo       | 2           |
| Charlie     | 2           |
| Delta       | null        |

推荐答案

请尝试执行此Mysql查询.这样可以解决您的问题.我们不需要员工表联接.如果您不从employee表中获取任何数据，则不要在join中添加employee表.

Please try this Mysql query. This will resolve your issue. We dont' require employee table join. If you are not taking any data from employee table then don't add employee table in join.

  SELECT 
    p.name AS ProjectName,
    Count( t.employeeID ) AS NumMembers 
  FROM 
    Project p 
    LEFT JOIN ProjectAssignment t ON p.id = t.projectID
  GROUP BY 
    p.name

输出:

Project name        NumMembers
Alpha                   3
Bravo                   2
Charlie                 2
Delta                   0

这篇关于用Count联接3个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！