时间戳

首页 > 解决方案 > 使用 Count 连接 3 个表

mysql - 使用 Count 连接 3 个表

问题描述

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

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        |

标签： mysqlsqljoincount

解决方案

请尝试这个 Mysql 查询。这将解决您的问题。我们不需要员工表连接。如果您没有从员工表中获取任何数据，则不要在联接中添加员工表。 

  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

推荐阅读