试试这个:
select count(a.id) num1,count(sign(instr(b.pack_desc,'补贴'))) num2
from tba a,tbt b
where a.pack_id = b.pack_id
and trunc(a.CLOSE_DATE) = trunc(sysdate-1)
select count(a.id) num1,count(sign(instr(b.pack_desc,'补贴'))) num2
from tba a,tbt b
where a.pack_id = b.pack_id
and trunc(a.CLOSE_DATE) = trunc(sysdate-1)
select count(a.id) num1,count(sign(instr(b.pack_desc,'补贴'))) num2
from tba a,tbt b
where a.pack_id = b.pack_id(+)
and trunc(a.CLOSE_DATE) = trunc(sysdate-1)
from tba a,tbt b
where a.pack_id = b.pack_id(+)
and trunc(a.CLOSE_DATE) = trunc(sysdate-1)
得到的记录是唯一的话改进如下:
select count(a.id),count(x.id)
from tba a,tba x,tbt t
where x.pack_id=t.pack_id and t.pack_desc like '%补贴%'
and to_char(x.CLOSE_DATE, 'yyyymmdd')=to_char(sysdate - 1, 'yyyymmdd')
and to_char(a.close_date,'yyyymmdd')=to_char(sysdate-1,'yyyymmdd')
当执行每条SQL语句时, ORACLE在内部执行了许多工作: 解析SQL语句, 估算索引的利用率, 绑定变量 , 读数据块等等. 由此可见, 减少访问数据库的次数 , 就能实际上减少ORACLE的工作量.
以下有两种方法可以检索出雇员号等于0342或0291的职员.
方法1 (低效) SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 342;
SELECT EMP_NAME , SALARY , GRADE FROM EMP WHERE EMP_NO = 291;
方法2 (高效) SELECT A.EMP_NAME , A.SALARY , A.GRADE, B.EMP_NAME , B.SALARY , B.GRADE FROM EMP A,EMP B WHERE A.EMP_NO = 342 AND B.EMP_NO = 291;