34,588
社区成员
发帖
与我相关
我的任务
分享
-- 要用复合索引
CREATE INDEX IX_view1
ON view1 (col3,col4,col5,col6,col2);
CREATE INDEX IX_tabl2
ON tabl2 (col3,col4,col5,col6,col2);
-- 查询
SELECT *
FROM view1 a
WHERE EXISTS (SELECT *
FROM view1 a1
WHERE a1.col3 = a.col3
AND a1.col4 = a.col4
AND a1.col5 = a.col5
AND a1.col6 = a.col6
AND a1.col2 >= 1
)
AND NOT EXISTS (SELECT *
FROM tabl2 b
WHERE b.col3 = a.col3
AND b.col4 = a.col4
AND b.col5 = a.col5
AND b.col6 = a.col6
AND b.col2 >= 10
)
on
A.col3=C.col3 and
A.col4=C.col4 and
A.col5=C.col5 AND
a.col6 =c.col6
少了 A.col2=C.col2 ? select * from view1 v
where v.col2>1
and not exists(select 1 from table2 t where v.col3 = t.col3 and .....)
这个方法挺好的,应该会提高查询效率
关于exists的效率问题请见http://www.cnblogs.com/ndxsdhy/archive/2010/12/11/1902904.htmlselect * from view1 v
where v.col2>1
and not exists(select * from table2 t where v.col3 = t.col3 and .....)