帮忙优化这一SQL语句,select sum(tbshipbill3_.WARE_TOTAL_AMOUNT) as col_0_0_ from TB_SHIPBILL tbshipbill0_, TB_SUB_PROMISE_PROTOCOL tbsubpromi1_, TB_PROMISE_PROTOCOL tbpromisep2_, TB_SHIPBILL tbshipbill3_
where tbsubpromi1_.SHIPBILL_ID=tbshipbill3_.ID and ((tbpromisep2_.START_SUBSECTION in(
select tdsubsecti0_.ID as col_0_0_ from TD_SUBSECTION_INFO tdsubsecti0_ where (tdsubsecti0_.BELONGTO_CITY='370100' )or(tdsubsecti0_.BELONGTO_CITY in(select tdcity1_.ID from TD_CITY tdcity1_ where (tdcity1_.BELONG_CITY='370100' )))
) and tbsubpromi1_.PROTOCOL_ID=tbpromisep2_.ID)and(tbpromisep2_.ARRIVE_SUBSECTION in(
select tdsubsecti0_.ID as col_0_0_ from TD_SUBSECTION_INFO tdsubsecti0_ where (tdsubsecti0_.BELONGTO_CITY='371100' )or(tdsubsecti0_.BELONGTO_CITY in(select tdcity1_.ID from TD_CITY tdcity1_ where (tdcity1_.BELONG_CITY='371100' )))
) and tbsubpromi1_.PROTOCOL_ID=tbpromisep2_.ID)and(tbpromisep2_.PROMISE_DATE
between to_date('2006-01-03','yyyy-MM-dd') and to_date('2006-01-14','yyyy-MM-dd') and tbsubpromi1_.PROTOCOL_ID=tbpromisep2_.ID))
有点晕了。
where tbsubpromi1_.SHIPBILL_ID=tbshipbill3_.ID and ((tbpromisep2_.START_SUBSECTION in(
select tdsubsecti0_.ID as col_0_0_ from TD_SUBSECTION_INFO tdsubsecti0_ where (tdsubsecti0_.BELONGTO_CITY='370100' )or(tdsubsecti0_.BELONGTO_CITY in(select tdcity1_.ID from TD_CITY tdcity1_ where (tdcity1_.BELONG_CITY='370100' )))
) and tbsubpromi1_.PROTOCOL_ID=tbpromisep2_.ID)and(tbpromisep2_.ARRIVE_SUBSECTION in(
select tdsubsecti0_.ID as col_0_0_ from TD_SUBSECTION_INFO tdsubsecti0_ where (tdsubsecti0_.BELONGTO_CITY='371100' )or(tdsubsecti0_.BELONGTO_CITY in(select tdcity1_.ID from TD_CITY tdcity1_ where (tdcity1_.BELONG_CITY='371100' )))
) and tbsubpromi1_.PROTOCOL_ID=tbpromisep2_.ID)and(tbpromisep2_.PROMISE_DATE
between to_date('2006-01-03','yyyy-MM-dd') and to_date('2006-01-14','yyyy-MM-dd') and tbsubpromi1_.PROTOCOL_ID=tbpromisep2_.ID))
有点晕了。
非本意
1、TB_SHIPBILL tbshipbill0_这个别名对应的表没有在where中使用,也就是说没有与其他表关联。
2、tbsubpromi1_.PROTOCOL_ID=tbpromisep2_.ID这个条件在where中出现3次,并且都是and连接。
select sum(tbshipbill3_.WARE_TOTAL_AMOUNT) as col_0_0_
from TB_SHIPBILL tbshipbill0_, TB_SUB_PROMISE_PROTOCOL tbsubpromi1_,
TB_PROMISE_PROTOCOL tbpromisep2_, TB_SHIPBILL tbshipbill3_
where tbsubpromi1_.SHIPBILL_ID=tbshipbill3_.ID
and (
(tbpromisep2_.START_SUBSECTION
in(
select tdsubsecti0_.ID as col_0_0_ from TD_SUBSECTION_INFO tdsubsecti0_
where (tdsubsecti0_.BELONGTO_CITY='370100' )
or (tdsubsecti0_.BELONGTO_CITY in (
select tdcity1_.ID from TD_CITY tdcity1_
where (tdcity1_.BELONG_CITY='370100' )
)
)
)
and tbsubpromi1_.PROTOCOL_ID=tbpromisep2_.ID
)
and (tbpromisep2_.ARRIVE_SUBSECTION
in(
select tdsubsecti0_.ID as col_0_0_ from TD_SUBSECTION_INFO tdsubsecti0_
where (tdsubsecti0_.BELONGTO_CITY='371100' )
or (tdsubsecti0_.BELONGTO_CITY in (
select tdcity1_.ID from TD_CITY tdcity1_
where (tdcity1_.BELONG_CITY='371100' )
)
)
)
and tbsubpromi1_.PROTOCOL_ID=tbpromisep2_.ID)
and( tbpromisep2_.PROMISE_DATE between to_date('2006-01-03','yyyy-MM-dd') and to_date('2006-01-14','yyyy-MM-dd')
and tbsubpromi1_.PROTOCOL_ID=tbpromisep2_.ID))