Select distinct a.AuLockNo,a.AuDoorName From jsAuthorizedObjects a, jsAuthorizedObjects b, jsAuthorizedObjects c Where a.AuUserNo<>b.AuUserNo and a.AuLockNo=b.AuLockNo and a.AuUserNo<>c.AuUserNo and a.AuLockNo=c.AuLockNo
Select distinct a.AuLockNo,a.AuDoorName From jsAuthorizedObjects a, jsAuthorizedObjects b, jsAuthorizedObjects c Where a.AuUserName='吕建华' AND B.AuUserName='屈玉山' AND C.AuUserName='杨方' and a.AuLockNo=b.AuLockNo and a.AuLockNo=c.AuLockNo
From jsAuthorizedObjects a,
jsAuthorizedObjects b,
jsAuthorizedObjects c
Where a.AuUserNo<>b.AuUserNo and a.AuLockNo=b.AuLockNo
and a.AuUserNo<>c.AuUserNo and a.AuLockNo=c.AuLockNo
From jsAuthorizedObjects a,
jsAuthorizedObjects b,
jsAuthorizedObjects c
Where a.AuUserName='吕建华' AND B.AuUserName='屈玉山' AND C.AuUserName='杨方' and a.AuLockNo=b.AuLockNo
and a.AuLockNo=c.AuLockNo
AuUserNo UsAuLockNo AuDoorName
----------- --------------------
4 25,26,27,28,29,30 210,209,211,212,213,214
select AuLockNo,AuDoorName from jsAuthorizedObjects
group by AuLockNo having count(AuLockNo)=n
多說一句:樓主的表好好象有違第三范式
select AuLockNo,AuDoorName from jsAuthorizedObjects
group by AuLockNo having count(AuLockNo)=n
多說一句:樓主的表 好象有違第三范式
楼主没有说不固定,你的语句在只有上面数据的情况下应该是正确的,不知道楼主是不是没有试就说不行!
如果不固定,jameszht(湖泊) 的回答比较适用。
关于范式,我觉得不必拘泥,不过AuCardNo AuUserNo AuUserName 三个字段这样重复肯定有違第三范式。
group by aulockno,udoorname having count(aulockno)=3
不知道我的思维是否正确,请大家多发表意见。