试试select a.* from eosoperator a, student b,classwhere c where a.operatorid = b.studentid and b.classid = c.classid and c.ishistoryclass=0
select a.* from eosoperator a,student b,class c where a.operatorid=b.studentid and b.classid=c.classid and c.ishistoryclass=0
select a.* from eosoperator a inner join student b on a.operatorid = b.studentid inner join classwhere c on b.classid = c.classid where c.ishistoryclass=0
用大家的方法,如果eosoperator表有1条记录,最终可能会查出10条来,,,
--tryselect a.* from eosoperator a inner join (select studentid from student b inner join classwhere c on b.classid = c.classid where c.ishistoryclass=0 group by studentid) as t on a.operatorid = t.studentid
select E.* from eosoperator E join (select distinct studentid from student B join (select classid from class where ishistoryclass=0) C on B.classid=C.classid) T on E.operatorid=T.studentid
联机帮助上不是有写么:当子查询里有in or exists语句,使用JOIN语句,效率要高么? 况且效率高不高和你的数据量,索引结构都有关。
用取真的方法测测: select * from eosoperator where exists(select 1 from class where ishistoryclass=0 and exists(select 1 from student where classid=class.classid and student=eosoperator.operatorid) )
select * from eosoperator where exists(select 1 from student where student=eosoperator.operatorid exists(select 1 from class where ishistoryclass=0 and classid=student.classid))
from eosoperator a, student b,classwhere c
where a.operatorid = b.studentid and b.classid = c.classid and c.ishistoryclass=0
and c.ishistoryclass=0
from eosoperator a
inner join student b on a.operatorid = b.studentid
inner join classwhere c on b.classid = c.classid
where c.ishistoryclass=0
from eosoperator a
inner join
(select studentid from student b inner join classwhere c on b.classid = c.classid where c.ishistoryclass=0 group by studentid) as t
on a.operatorid = t.studentid
from eosoperator E join
(select distinct studentid
from student B join (select classid from class where ishistoryclass=0) C on B.classid=C.classid) T
on E.operatorid=T.studentid
况且效率高不高和你的数据量,索引结构都有关。
select
*
from
eosoperator
where
exists(select 1 from class where ishistoryclass=0 and
exists(select 1 from student where classid=class.classid and student=eosoperator.operatorid) )
*
from
eosoperator
where
exists(select 1 from student where student=eosoperator.operatorid
exists(select 1 from class where ishistoryclass=0 and classid=student.classid))