select a.isconfirm,
a.vasid,
decode(a.ifbingdan,
0,
a.vaserial,
1,
v1.vaserial || '<br>' || a.vaserial,
2,
v2.vaserial || '<br>' || v1.vaserial || '<br>' || a.vaserial,
3,
v3.vaserial || '<br>' || v2.vaserial || '<br>' || v1.vaserial ||
'<br>' || a.vaserial,
4,
v4.vaserial || '<br>' || v3.vaserial || '<br>' || v2.vaserial ||
'<br>' || v1.vaserial || '<br>' || a.vaserial) vaserial,
a.vaid,
b.vserial,
a.abnormal,
a.vid,
c.license,
i.vdname,
a.isover,
a.vspid,
h.vspname,
a.bvaserial,
b.usetime,
to_char(b.usetime, 'mm-dd hh24:mi') as strUsetime,
a.endtime,
a.vastatus,
a.vauserid,
e.autonym vauser,
a.buildtime,
a.modifytime,
a.muserid,
a.assess,
a.assdetail,
a.asuserid,
decode(a.ifbingdan,
0,
b.contactperson,
1,
b1.contactperson || '<br>' || b.contactperson,
2,
b2.contactperson || '<br>' || b1.contactperson || '<br>' ||
b.contactperson,
3,
b3.contactperson || '<br>' || b2.contactperson || '<br>' ||
b1.contactperson || '<br>' || b.contactperson,
4,
b4.contactperson || '<br>' || b3.contactperson || '<br>' ||
b2.contactperson || '<br>' || b1.contactperson || '<br>' ||
b.contactperson) contactperson,
decode(a.ifbingdan,
0,
b.dname,
1,
b1.dname || '<br>' || b.dname,
2,
b2.dname || '<br>' || b1.dname || '<br>' || b.dname,
3,
b3.dname || '<br>' || b2.dname || '<br>' || b1.dname || '<br>' ||
b.dname,
4,
b4.dname || '<br>' || b3.dname || '<br>' || b2.dname || '<br>' ||
b1.dname || '<br>' || b.dname) dname,
decode(a.ifbingdan,
0,
j.vatname,
1,
j1.vatname || '<br>' || j.vatname,
2,
j2.vatname || '<br>' || j1.vatname || '<br>' || j.vatname,
3,
j3.vatname || '<br>' || j2.vatname || '<br>' || j1.vatname ||
'<br>' || j.vatname,
4,
j4.vatname || '<br>' || j3.vatname || '<br>' || j2.vatname ||
'<br>' || j1.vatname || '<br>' || j.vatname) vatname,
a.ifbingdan
from vehicle_assign a
left join (select vaserial, bvaserial, vaid
from (select vaserial,
bvaserial,
vaid,
row_number() over(partition by bvaserial order by modifytime) as rown
from vehicle_assign)
where rown = 1) v1
on a.vaserial = v1.bvaserial
left join (select vaserial, bvaserial, vaid
from (select vaserial,
bvaserial,
vaid,
row_number() over(partition by bvaserial order by modifytime) as rown
from vehicle_assign)
where rown = 2) v2
on a.vaserial = v2.bvaserial
left join (select vaserial, bvaserial, vaid
from (select vaserial,
bvaserial,
vaid,
row_number() over(partition by bvaserial order by modifytime) as rown
from vehicle_assign)
where rown = 3) v3
on a.vaserial = v3.bvaserial
left join (select vaserial, bvaserial, vaid
from (select vaserial,
bvaserial,
vaid,
row_number() over(partition by bvaserial order by modifytime) as rown
from vehicle_assign)
where rown = 4) v4
on a.vaserial = v4.bvaserial
left outer join vehicle_apply b
on a.vaid = b.vaid
left outer join vehicle_apply b1
on v1.vaid = b1.vaid
left outer join vehicle_apply b2
on v2.vaid = b2.vaid
left outer join vehicle_apply b3
on v3.vaid = b3.vaid
left outer join vehicle_apply b4
on v4.vaid = b4.vaid
left outer join vehicle c
on a.vid = c.vid
left outer join userinfo e
on a.vauserid = e.userid
left outer join v_spot h
on a.vspid = h.vspid
left outer join vdriver i
on a.drivers = to_char(i.vdid)
left outer join va_tasktype j
on j.vatid = b.vatid
left outer join va_tasktype j1
on j1.vatid = b1.vatid
left outer join va_tasktype j2
on j2.vatid = b2.vatid
left outer join va_tasktype j3
on j3.vatid = b3.vatid
left outer join va_tasktype j4
on j4.vatid = b4.vatid
left outer join department m
on c.did = m.did
where b.destype = 0
and a.vspid = 98
and a.vastatus < 5
and a.bvaserial is null
求这句sql优化,时间太长了用8秒多,请高人具体指点
a.vasid,
decode(a.ifbingdan,
0,
a.vaserial,
1,
v1.vaserial || '<br>' || a.vaserial,
2,
v2.vaserial || '<br>' || v1.vaserial || '<br>' || a.vaserial,
3,
v3.vaserial || '<br>' || v2.vaserial || '<br>' || v1.vaserial ||
'<br>' || a.vaserial,
4,
v4.vaserial || '<br>' || v3.vaserial || '<br>' || v2.vaserial ||
'<br>' || v1.vaserial || '<br>' || a.vaserial) vaserial,
a.vaid,
b.vserial,
a.abnormal,
a.vid,
c.license,
i.vdname,
a.isover,
a.vspid,
h.vspname,
a.bvaserial,
b.usetime,
to_char(b.usetime, 'mm-dd hh24:mi') as strUsetime,
a.endtime,
a.vastatus,
a.vauserid,
e.autonym vauser,
a.buildtime,
a.modifytime,
a.muserid,
a.assess,
a.assdetail,
a.asuserid,
decode(a.ifbingdan,
0,
b.contactperson,
1,
b1.contactperson || '<br>' || b.contactperson,
2,
b2.contactperson || '<br>' || b1.contactperson || '<br>' ||
b.contactperson,
3,
b3.contactperson || '<br>' || b2.contactperson || '<br>' ||
b1.contactperson || '<br>' || b.contactperson,
4,
b4.contactperson || '<br>' || b3.contactperson || '<br>' ||
b2.contactperson || '<br>' || b1.contactperson || '<br>' ||
b.contactperson) contactperson,
decode(a.ifbingdan,
0,
b.dname,
1,
b1.dname || '<br>' || b.dname,
2,
b2.dname || '<br>' || b1.dname || '<br>' || b.dname,
3,
b3.dname || '<br>' || b2.dname || '<br>' || b1.dname || '<br>' ||
b.dname,
4,
b4.dname || '<br>' || b3.dname || '<br>' || b2.dname || '<br>' ||
b1.dname || '<br>' || b.dname) dname,
decode(a.ifbingdan,
0,
j.vatname,
1,
j1.vatname || '<br>' || j.vatname,
2,
j2.vatname || '<br>' || j1.vatname || '<br>' || j.vatname,
3,
j3.vatname || '<br>' || j2.vatname || '<br>' || j1.vatname ||
'<br>' || j.vatname,
4,
j4.vatname || '<br>' || j3.vatname || '<br>' || j2.vatname ||
'<br>' || j1.vatname || '<br>' || j.vatname) vatname,
a.ifbingdan
from vehicle_assign a
left join (select vaserial, bvaserial, vaid
from (select vaserial,
bvaserial,
vaid,
row_number() over(partition by bvaserial order by modifytime) as rown
from vehicle_assign)
where rown = 1) v1
on a.vaserial = v1.bvaserial
left join (select vaserial, bvaserial, vaid
from (select vaserial,
bvaserial,
vaid,
row_number() over(partition by bvaserial order by modifytime) as rown
from vehicle_assign)
where rown = 2) v2
on a.vaserial = v2.bvaserial
left join (select vaserial, bvaserial, vaid
from (select vaserial,
bvaserial,
vaid,
row_number() over(partition by bvaserial order by modifytime) as rown
from vehicle_assign)
where rown = 3) v3
on a.vaserial = v3.bvaserial
left join (select vaserial, bvaserial, vaid
from (select vaserial,
bvaserial,
vaid,
row_number() over(partition by bvaserial order by modifytime) as rown
from vehicle_assign)
where rown = 4) v4
on a.vaserial = v4.bvaserial
left outer join vehicle_apply b
on a.vaid = b.vaid
left outer join vehicle_apply b1
on v1.vaid = b1.vaid
left outer join vehicle_apply b2
on v2.vaid = b2.vaid
left outer join vehicle_apply b3
on v3.vaid = b3.vaid
left outer join vehicle_apply b4
on v4.vaid = b4.vaid
left outer join vehicle c
on a.vid = c.vid
left outer join userinfo e
on a.vauserid = e.userid
left outer join v_spot h
on a.vspid = h.vspid
left outer join vdriver i
on a.drivers = to_char(i.vdid)
left outer join va_tasktype j
on j.vatid = b.vatid
left outer join va_tasktype j1
on j1.vatid = b1.vatid
left outer join va_tasktype j2
on j2.vatid = b2.vatid
left outer join va_tasktype j3
on j3.vatid = b3.vatid
left outer join va_tasktype j4
on j4.vatid = b4.vatid
left outer join department m
on c.did = m.did
where b.destype = 0
and a.vspid = 98
and a.vastatus < 5
and a.bvaserial is null
求这句sql优化,时间太长了用8秒多,请高人具体指点
left outer join vehicle_apply b1 on v1.vaid = b1.vaid
left outer join vehicle_apply b2 on v2.vaid = b2.vaid
left outer join vehicle_apply b3 on v3.vaid = b3.vaid
left outer join vehicle_apply b4 on v4.vaid = b4.vaid
-- vehicle_apply 这张表不需要 join 5次,只需要join 一次就够啦!
bvaserial,
vaid,
row_number() over(partition by bvaserial order by modifytime) as rown
from vehicle_assign这部分重复查询过多次,增加表的扫描次数,可以用with子句定义,格式:
WITH
subquery_name
AS
(the aggregation SQL statement)
SELECT
(query naming subquery_name);