查找出现在SQL服务器中所有部门的所有用户?
问题描述:
我有表Users
查找出现在SQL服务器中所有部门的所有用户?
我也有表Departments
我有Users
和Departments
之间的映射表。
我想找到的所有用户名和标识出现在所有部门。
例如,如果有5个部门,Paul只在部门#1中,所以Paul不会出现在输出列表中。
他会的,只有当他在各部门(1..5)上市
我开始使用临时表做一些事情,这是非常长(readly长),我认为有一个更好的办法。
我也创建了一个Sql Fiddle。
答
有多种方法可以做到这一点。
您可以要求用户在部门的数量等于部门总数:
SELECT
*
FROM
Users
INNER JOIN
(
SELECT userId, COUNT(*) c FROM MapUserstoDepartments
GROUP BY userId
HAVING COUNT(*) = (SELECT COUNT(*) FROM Departments)
) UsersInAllDepartments
ON Users.userId = UsersInAllDepartments.userId
你可能需要从各部门的列表中删除用户的部门留下任何:
SELECT *
FROM Users
WHERE NOT EXISTS
(
SELECT depId FROM Departments
EXCEPT
SELECT depId FROM MapUserstoDepartments WHERE userId = Users.userId
)
我确定还有其他人。
答
这是你想要的吗?
Select Tbl.userID , Tbl.username from (Select u.userid , u.username ,
count(u.userid) as Count from MapUsersToDepartments m
inner join Users u on m.UserID = u.userID
group by u.userid , u.username)Tbl
where Tbl.Count = (Select count(*) from Departments)
这里是小提琴
答
试试这个
SELECT u.userId, u.UserName
FROM MapUserstoDepartments m INNER JOIN
Users u ON u.userId = m.userId
GROUP BY u.userId, u.UserName
HAVING COUNT(m.depId) = (SELECT COUNT(*) FROM Departments)
这将产生
| USERID | USERNAME |
---------------------
| 100 | John |
答
select
Users.userId,
count(Departments.depId),
count(MapUserstoDepartments.userId)
from
Users
left join MapUserstoDepartments on MapUserstoDepartments.userId = Users.userId
left join Departments on Departments.depId = MapUserstoDepartments.depId
group by
Users.userId
having
(SELECT COUNT(*) from Departments) = count(MapUserstoDepartments.userId)
答
你可以这样说:
select u.*
from Users u
where not exists
(
select 1
from Departments d
where not exists
(
select 1
from MapUserstoDepartments m
where d.depId = m.depId
and m.userId = u.userId
)
)
答
可以尝试下面的查询。让我知道这是否会有所帮助
select * from users where userid in
(select userid from MapUserstoDepartments
group by userid
having count(userid) = 5)