EXISTS的应用

一个用户具有多个角色，请查询出该表中具有该用户的所有角色的其他用户。

CREATE TABLE `user` (

  `uid` int(11) NOT NULL,
  `uname` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`uid`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `role` (
  `rid` int(11) NOT NULL,
  `rname` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `ur` (
  `uid` int(11) DEFAULT NULL,
  `rid` int(11) DEFAULT NULL,
  KEY `urr` (`uid`),
  KEY `rrr` (`rid`),
  CONSTRAINT `rrr` FOREIGN KEY (`rid`) REFERENCES `role` (`rid`),
  CONSTRAINT `urr` FOREIGN KEY (`uid`) REFERENCES `user` (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

SELECT *
from `user` u0
where not EXISTS (
select rid
from ur u1
where u1.uid=1 and not EXISTS 
(SELECT *
from ur u2
where u2.uid=u0.uid
and u2.rid=u1.rid))

select *
from `user` u0
where not EXISTS(
SELECT *
FROM ur u1
where u1.uid=1 and u1.rid
not in(
SELECT rid
from ur u2
where u2.uid=u0.uid))