mysql - 左联接四个表，结果不理想

我做了一个小项目经理。我需要知道有多少个打开的项目分配给特定用户组的用户。

我创建了四个表：

使用者

╔════╦══════════╦══════════════╗
║ id ║ group_id ║ full_name    ║
╠════╬══════════╬══════════════╣
║  1 ║     4    ║ Jescie Head  ║
╠════╬══════════╬══════════════╣
║  2 ║     1    ║ Amity Mooney ║
╠════╬══════════╬══════════════╣
║  3 ║     1    ║ Ivy Yates    ║
╠════╬══════════╬══════════════╣
║  4 ║     1    ║ Bo Goff      ║
╚════╩══════════╩══════════════╝

专案

╔════╦══════╦════════╗
║ id ║ code ║ status ║
╠════╬══════╬════════╣
║  1 ║ P001 ║    0   ║
╠════╬══════╬════════╣
║  2 ║ P002 ║    1   ║
╚════╩══════╩════════╝

团体

╔════╦══════╦═════════╦════════╗
║ id ║ type ║ name    ║ status ║
╠════╬══════╬═════════╬════════╣
║  1 ║   0  ║ Group 1 ║    1   ║
╠════╬══════╬═════════╬════════╣
║  2 ║   2  ║ Group 2 ║    1   ║
╠════╬══════╬═════════╬════════╣
║  4 ║   1  ║ Group 4 ║    1   ║
╚════╩══════╩═════════╩════════╝

project_user

╔═════════╦════════════╗
║ id_user ║ id_project ║
╠═════════╬════════════╣
║    5    ║      2     ║
╠═════════╬════════════╣
║    4    ║      2     ║
╠═════════╬════════════╣
║    3    ║      2     ║
╠═════════╬════════════╣
║    4    ║      1     ║
╚═════════╩════════════╝

我希望找到分配给第1组（类型0）用户的活动项目。我的查询有效，显示合适的用户，但也计数状态为0的项目：

SELECT u.id,
       u.full_name,
       u.type,
       Count(pu.id_project) AS assigned_projects
FROM   users AS u
       LEFT JOIN project_user AS pu
              ON u.id = pu.id_user
       JOIN groups AS g
         ON g.id = u.group_id
            AND g.type = 0
       LEFT JOIN projects AS p
              ON pu.id_project = p.id
                 AND p.status = 1
GROUP  BY u.id
ORDER  BY u.type, u.id ASC

更新

当前结果：

╔════╦══════════════╦══════╦═══════════════════╗
║ id ║   full_name  ║ type ║ assigned_projects ║
╠════╬══════════════╬══════╬═══════════════════╣
║  2 ║ Amity Mooney ║   3  ║         0         ║
╠════╬══════════════╬══════╬═══════════════════╣
║  3 ║   Ivy Yates  ║   6  ║         1         ║
╠════╬══════════════╬══════╬═══════════════════╣
║  4 ║    Bo Goff   ║   1  ║         2         ║
╚════╩══════════════╩══════╩═══════════════════╝

想要的结果：原因项目2已关闭。

╔════╦══════════════╦══════╦═══════════════════╗
║ id ║   full_name  ║ type ║ assigned_projects ║
╠════╬══════════════╬══════╬═══════════════════╣
║  2 ║ Amity Mooney ║   3  ║         0         ║
╠════╬══════════════╬══════╬═══════════════════╣
║  3 ║   Ivy Yates  ║   6  ║         0         ║
╠════╬══════════════╬══════╬═══════════════════╣
║  4 ║    Bo Goff   ║   1  ║         1         ║
╚════╩══════════════╩══════╩═══════════════════╝

DB小提琴：DB Fiddle

最佳答案

我认为您不应该在连接“项目”表时使用左联接。此外，您的按行分组应该是固定的。请尝试下面的查询。

SELECT u.id,u.full_name,u.type,Count(pu.id_project) AS assigned_projects
FROM users AS u
JOIN groups AS g ON g.id = u.group_id AND g.type = 0
LEFT JOIN project_user AS pu ON u.id = pu.id_user
JOIN projects AS p ON pu.id_project = p.id AND p.status = 1
GROUP BY u.id,u.full_name,u.type
ORDER  BY u.type, u.id ASC

编辑：如果您想查看所有用户，即使没有为该用户分配活动项目，则您需要在下面使用（用大写字母SUM代替COUNT并删除联接行中的状态检查）：

SELECT u.id,u.full_name,u.type,SUM(CASE WHEN p.status = 1 then 1 else 0 end) AS assigned_projects
FROM users AS u
JOIN groups AS g ON g.id = u.group_id AND g.type = 0
LEFT JOIN project_user AS pu ON u.id = pu.id_user
JOIN projects AS p ON pu.id_project = p.id
GROUP BY u.id,u.full_name,u.type
ORDER  BY u.type, u.id ASC

编辑2：这是在MSSQL中准备的示例数据。您将需要为mysql删除＃个符号。

create table #users (id int, groupid int, fullname varchar(50))
create table #groups (id int, [type] int, name varchar(50),[status] bit)
create table #projects (id int, code varchar(50), [status] bit)
create table #project_user (id_user int , id_project int)

insert into #users values (1,4,'Jescie'),(2,1,'Amity'),(3,1,'Ivy'),(4,1,'Jesse')
insert into #projects values (1,'p001',0),(2,'p002',1)
insert into #groups values (1,0,'G1',1),(2,2,'G2',1),(4,1,'G4',1)
insert into #project_user values (5,2),(4,2),(3,2),(4,1)

SELECT u.id,u.fullname,g.[type], SUM(CASE WHEN p.status = 1 then 1 else 0 end) AS assigned_projects
FROM #users AS u
JOIN #groups AS g ON g.id = u.groupid AND g.type = 0
LEFT JOIN #project_user AS pu ON u.id = pu.id_user
LEFT JOIN #projects AS p ON pu.id_project = p.id
GROUP BY u.id,u.fullname,g.[type]
ORDER  BY g.[type], u.id ASC

关于mysql - 左联接四个表，结果不理想，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/54095225/