创建一个表,临时使用
crteate table t as select select seriesuid from series where modality='MR' and exists (select studyuid from studies where studies.studyuid=series.studyuid and patientid in ('0203','0267','0377','0449','0185','0402'));然后:
select * from images where not exists (select seriesuid from t where modality='MR' and images.seriesuid=t.seriesuid);
crteate table t as select select seriesuid from series where modality='MR' and exists (select studyuid from studies where studies.studyuid=series.studyuid and patientid in ('0203','0267','0377','0449','0185','0402'));然后:
select * from images where not exists (select seriesuid from t where modality='MR' and images.seriesuid=t.seriesuid);
select seriesuid from series a,studies b
where a.studyuid=b.studyuid
and c.seriesuid=a.seriesuid
and a.modality='MR'
and b.patientid in ('0203','0267','0377','0449','0185','0402'))
From后面的表按照记录从多少少来排序Where后面如果有连接表则把连接条件写前面,其他的条件按照所排除数据的多少由大到小排,比如a.modality='MR'可以排除55%的记录,b.patientid in 可以排除20%的记录
select * from images c,(
select seriesuid from series a,studies b
where a.studyuid=b.studyuid
and a.modality='MR'
and b.patientid in ('0203','0267','0377','0449','0185','0402')) d
where c.seriesuid(+)=d.seriesuid and c.seriesuid is null
其中a表有1000000条记录,b表有1000条记录
select c.* from images c,(select a.seriesuid from series a,studies b where a.modality='MR' and a.studyuid = b.studyuid and b.patientid in('0203','0267','0377','0449','0185','0402')) d
where c.seriesuid = d.seriesuid(+) and d.seriesuid is null