with emp as (select empid from emporg where exists(select 1 from (select orgid from jgxxb start with orgid='05950001' connect by prior ORGID = PARENT ) orgs where emporg.orgid=orgs.orgid ) )
select m.*, n.bal
from (select a.orgid ,
e.empname ,
b.orgname ,
a.card ,
a.input ,
a.home ,
a.type ,
c.tname ,
a.saltype ,
cast(round(nvl(sum(a.ave), 0) / (4), 2) as
numeric(10, 2)) ,
d.sysnm ,
a.tno ,
a.custname
a.opendate
from saldtl a,
organ b,
svt c,
sysmap d,
emps e
where a.tno = c.tno(+)
and a.sysid = d.sysid(+)
and a.empid = e.empid
and a.openorg = b.org
and date >= '201001'
and date <= '201004'
and exists (select 1 from emp where a.empid = emp.empid)
group by a.orgid,
e.empname,
a.custname,
a.card,
b.orgname,
a.input,
a.home,
c.tname,
a.type,
a.saltype,
d.sysnm,
a.tno,
a.opendate) m,
saldtl n
where m.card = n.card
and n.date = '201004'
order by m.orgid, m.empname, m.orgname, m.type求优化该sql,我跑着感觉慢,不懂如何优化
select m.*, n.bal
from (select a.orgid ,
e.empname ,
b.orgname ,
a.card ,
a.input ,
a.home ,
a.type ,
c.tname ,
a.saltype ,
cast(round(nvl(sum(a.ave), 0) / (4), 2) as
numeric(10, 2)) ,
d.sysnm ,
a.tno ,
a.custname
a.opendate
from saldtl a,
organ b,
svt c,
sysmap d,
emps e
where a.tno = c.tno(+)
and a.sysid = d.sysid(+)
and a.empid = e.empid
and a.openorg = b.org
and date >= '201001'
and date <= '201004'
and exists (select 1 from emp where a.empid = emp.empid)
group by a.orgid,
e.empname,
a.custname,
a.card,
b.orgname,
a.input,
a.home,
c.tname,
a.type,
a.saltype,
d.sysnm,
a.tno,
a.opendate) m,
saldtl n
where m.card = n.card
and n.date = '201004'
order by m.orgid, m.empname, m.orgname, m.type求优化该sql,我跑着感觉慢,不懂如何优化
今天又折腾下,这条sql居然跑不动了,sql就没改
saldtl这个是个大表,百度一番,exists适合外表大的,然后改成用IN,SQL又能跑了。
and exists (select 1 from emp where a.empid = emp.empid)
向这一行是不是改成直接表连接会好点呢? and a.empid=emp.empid
另外今天看到这么2条介绍:
18.用EXISTS替代IN 在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接。在这种情况下,使用EXISTS(或NOT EXISTS)通常将提高查询的效率。20.用表连接替换EXISTS 通常来说 , 采用表连接的方式比EXISTS更有效率这2条是不是有矛盾呢?有高人解释下不?