我做了一个小项目经理。我需要知道有多少个打开的项目分配给特定用户组的用户。
我创建了四个表：
使用者
╔════╦══════════╦══════════════╗
║ 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/