in 比 直接关联效率高
如:
select g.examid as examid,
g.code_course as code_course,
i.sackid as sackid,
g.permitid as permitid,
g.name_cn as name_cn,
g.inputno as inputno,
g.operator as operator,
convert(integer,g.smallid) as smallid,
g.grademark as grademark
from exam_grade_small g,
exam_stusackinfo i
where g.examid = 1
and g.code_course = '002'
and (g.operator='9999' or g.operator='8888')
and g.examid = i.examid
and g.code_course = i.code_course
and g.permitid between i.startpermitid and i.endpermitid
and exists(
select 1 from
(select a.examid,a.code_course,a.permitid,a.smallid
from exam_grade_small a
where a.examid = 1
and a.code_course = '002'
and (a.operator='9999' or a.operator='8888')
group by a.examid,a.code_course,a.permitid,a.smallid
having count(distinct a.grademark) > 1 or count(distinct a.operator) = 1) h
where
g.examid = h.examid
and g.code_course = h.code_course
and g.permitid = h.permitid
and g.smallid = h.smallid
)
--执行时间0.078秒
select g.examid as examid,
g.code_course as code_course,
i.sackid as sackid,
g.permitid as permitid,
g.name_cn as name_cn,
g.inputno as inputno,
g.operator as operator,
convert(integer,g.smallid) as smallid,
g.grademark as grademark
from exam_grade_small g,(select a.examid,a.code_course,a.permitid,a.smallid
from exam_grade_small a
where a.examid = 1
and a.code_course = '001'
and (a.operator='9999' or a.operator='8888')
group by a.examid,a.code_course,a.permitid,a.smallid
having count(distinct a.grademark) > 1 or count(distinct a.operator) = 1) h,
exam_stusackinfo i
where g.examid = h.examid
and g.code_course = h.code_course
and g.permitid = h.permitid
and g.smallid = h.smallid
and g.examid = 1
and g.code_course = '001'
and (g.operator='9999' or g.operator='8888')
and h.examid = i.examid
and h.code_course = i.code_course
and h.permitid between i.startpermitid and i.endpermitid
--执行时间超过30秒