UC知道谁帮我优化下这条sql
来源：百度知道 编辑：UC知道 时间：2024/06/30 01:32:15
select decode(x.group_name, null, y.group_name, x.group_name) as 名称,
decode(x.op_time, null, y.op_time, x.op_time) as 日期,
nvl(x.store_fee, 0) as 收欠费,
nvl(x.pecc_fee, 0) as 违约金,
nvl(x.this_store, 0) as 话费预存,
y.op_fee_in as 业务受理,
y.op_fee_out as 补退费,
nvl(x.store_fee, 0) + nvl(x.pecc_fee, 0) + nvl(x.this_store, 0) +
nvl(y.op_fee_in, 0) + nvl(y.op_fee_out, 0) as 合计
from (select b.group_name group_name,
to_char(a.op_time, 'yyyy-mm-dd') op_time,
sum(decode(a.op_mode, '06', a.this_store, 0)) store_fee,
sum(decode(a.op_mode, '06', a.pecc_fee, 0)) pecc_fee,
sum(decode(a.op_mode, '08', a.this_store, 0)) this_store
from dChnBFeeDet a, dchngroupmsg b
where a.is_back = '01'
and a.group_id = b.group_id
and b.group_id in
decode(x.op_time, null, y.op_time, x.op_time) as 日期,
nvl(x.store_fee, 0) as 收欠费,
nvl(x.pecc_fee, 0) as 违约金,
nvl(x.this_store, 0) as 话费预存,
y.op_fee_in as 业务受理,
y.op_fee_out as 补退费,
nvl(x.store_fee, 0) + nvl(x.pecc_fee, 0) + nvl(x.this_store, 0) +
nvl(y.op_fee_in, 0) + nvl(y.op_fee_out, 0) as 合计
from (select b.group_name group_name,
to_char(a.op_time, 'yyyy-mm-dd') op_time,
sum(decode(a.op_mode, '06', a.this_store, 0)) store_fee,
sum(decode(a.op_mode, '06', a.pecc_fee, 0)) pecc_fee,
sum(decode(a.op_mode, '08', a.this_store, 0)) this_store
from dChnBFeeDet a, dchngroupmsg b
where a.is_back = '01'
and a.group_id = b.group_id
and b.group_id in
排里面做过子查询的地方的select都在执行这个SQL之前生成临时表，
否则每次连接时都会执行子查询的select，
非常耗时。
生成临时表后，
每次是直接查询临时表的结果。
另外...字段 in (select ...) 可以改为
...exists(select ... where a.字段 = b.字段)
如果看不明白的话，
可以查一下exists的语法，
只返回一个布尔值。
哥们你给出这样一张SQL是没有人能帮你优化好的.你得把业务须要说出来.把这条SQL关联的表介绍一下