select user.id,user.name from user where user.id in( select userid,count(roleid) from 中间表 where count(roleid)>1 group by userid);--等大牛
select * from [user] as a where not exists(select 1 from [role] as b where not exists(select 1 from 中间表 where userID=a.userID and roleID=b.roleID))
或 select a.ID,a.Name from [user] as a inner join [中间表] as b on a.userID=b.userID inner join [role] as c on c.roleID=b.roleID where c.roleName in('普通','管理员') group by a.ID,a.Name having count(1)=2
role--有多个角色时把加条件加上b.roleName in('普通','管理员') select * from [user] as a where not exists(select 1 from [role] as b where b.roleName in('普通','管理员') and not exists(select 1 from 中间表 where userID=a.userID and roleID=b.roleID))
select distinct a.name from [user] a inner join 中间表 b on a.id=b.userid inner join [role] c on c.id=b.roleid inner join [role] d on d.id=b.roleid where c.roleName='普通' and d.roleName='管理员'
select * from [user] as a where not exists (select 1 from [role] as b where b.roleName in('普通','管理员') and not exists(select 1 from 中间表 where userID=a.userID and roleID=b.roleID)) 这个查询速度应该是最快的
如果用连接,应该这样写: select d.name from [role] a inner join 中间表 b on a.id=b.roleid inner join 中间表 c on b.userid=c.userid and b.roleid<>c.roleid inner join [user] d on b.userid=d.id where a.roleName='普通'
select user.id,user.name from user
where user.id in(
select userid,count(roleid) from 中间表
where count(roleid)>1 group by userid);--等大牛
select a.ID,a.Name
from [user] as a
inner join [中间表] as b on a.userID=b.userID
inner join [role] as c on c.roleID=b.roleID
where c.roleName in('普通','管理员')
group by a.ID,a.Name
having count(1)=2
select * from [user] as a where not exists(select 1 from [role] as b where b.roleName in('普通','管理员') and not exists(select 1 from 中间表 where userID=a.userID and roleID=b.roleID))
from [user] a inner join 中间表 b on a.id=b.userid
inner join [role] c on c.id=b.roleid
inner join [role] d on d.id=b.roleid
where c.roleName='普通' and d.roleName='管理员'
select * from [user] as a where not exists
(select 1 from [role] as b where b.roleName in('普通','管理员')
and not exists(select 1 from 中间表 where userID=a.userID and roleID=b.roleID))
这个查询速度应该是最快的
select d.name from [role] a inner join 中间表 b on a.id=b.roleid
inner join 中间表 c on b.userid=c.userid and b.roleid<>c.roleid
inner join [user] d on b.userid=d.id
where a.roleName='普通'