select id from asm where where inspection_id in(select inspection_id from (select rownum numrow,inspection_Id from ( select inspection_id from asm where (re!='new' or re is null)) where (numrow>0 adn numrow<=10) order by inspection_end_tem desc
SELECT * FROM (SELECT TEMPED__TABLE. *, ROWNUM TEMPED__NUM FROM (select id from asm where re != 'NEW' or re is null order by INSPECTION_END_TIME DESC) TEMPED__TABLE WHERE ROWNUM <= 10) WHERE TEMPED__NUM >= 0;
呵呵 好像漏了个条件: SELECT * FROM (SELECT TEMPED__TABLE. *, ROWNUM TEMPED__NUM FROM (select id from asm where (re != 'NEW' or re is null) and 1 = 1 order by INSPECTION_END_TIME DESC) TEMPED__TABLE WHERE ROWNUM <= 10) WHERE TEMPED__NUM >= 0;
首先尽量不要用in操作 例:select abc from a where abc in (select abc from b) 替代方法1: select a.abc from a,b where a.abc=b.abc(推荐) 替代方法2: select abc from a where exists (select b.abc from b where a.abc=b.abc) 可以提高效率 对于本查询要分页效果则根本不需要in....
FROM (SELECT TEMPED__TABLE. *, ROWNUM TEMPED__NUM
FROM (select id
from asm
where re != 'NEW' or re is null
order by INSPECTION_END_TIME DESC) TEMPED__TABLE
WHERE ROWNUM <= 10)
WHERE TEMPED__NUM >= 0;
SELECT *
FROM (SELECT TEMPED__TABLE. *, ROWNUM TEMPED__NUM
FROM (select id
from asm
where (re != 'NEW' or re is null) and 1 = 1
order by INSPECTION_END_TIME DESC) TEMPED__TABLE
WHERE ROWNUM <= 10)
WHERE TEMPED__NUM >= 0;
看下是否能用上索引:
例:select abc from a where abc in (select abc from b)
替代方法1: select a.abc from a,b where a.abc=b.abc(推荐)
替代方法2: select abc from a where exists (select b.abc from b where a.abc=b.abc)
可以提高效率
对于本查询要分页效果则根本不需要in....
我根据大家建议测试后再相告:)ps:关于索引.不能使用过多.因为还有程序在大批量写入数据到此表:(