select AuLockNo,AuDoorName
from jsAuthorizedObjects
where AuUserName='吕建华' and AuLockNo in
(select AuLockNo,AuDoorName
from jsAuthorizedObjects
where AuUserName='屈玉山' and AuLockNo in
(select AuLockNo,AuDoorName
from jsAuthorizedObjects
where AuUserName='杨方'
)
)
from jsAuthorizedObjects
where AuUserName='吕建华' and AuLockNo in
(select AuLockNo,AuDoorName
from jsAuthorizedObjects
where AuUserName='屈玉山' and AuLockNo in
(select AuLockNo,AuDoorName
from jsAuthorizedObjects
where AuUserName='杨方'
)
)
from jsAuthorizedObjects
where (AuLockNo in
(Select AuLockNo from jsAuthorizedObjects Where
AuUserName='吕建华' )
) and
(AuLockNo in
(Select AuLockNo from jsAuthorizedObjects Where
AuUserName='屈玉山' )
)
plainsong的不行,它是把吕建华、屈玉山能够开的门分别列出来。
from jsAuthorizedObjects a1
JOIN jsAuthorizedObjects a2 ON a1.AuLockNo = a2.AuLockNo
AND a1.AuUserNo <> a2.AuUserNO and a1.AuUserNo = '4'如果再多人的话一句好象是写不出来的,必须先找到有最少AuLockNo数量
的AuUserNo才行。