怎样解决“使用case...when...导致的性能低下”的问题呢？

[ypopo1234]

以下语句执行下来需要2.3秒，相当的慢，主要在于使用了三个case when,去掉这三个case ，执行速度还是没问题，怎么修改
三个case...when...，使之能达到同样的效果呢，谢谢你们。

select case when t.je1 is null then
(select sum(t1.TOTAL_PRICE)
from FWLR_YJ t1
where t1.HTL_DD_ID = t.id
and t1.type = '吃饭')
else
(select sum(t2.TOTAL_PRICE)
from FWLR_SJ t2
where t2.HTL_DD_ID = t.id
and t2.type = '吃饭')
end as cyFeeSum,
case
when t.je1 is null then
(select sum(t1.TOTAL_PRICE)
from FWLR_YJ t1
where t1.HTL_DD_ID = t.id
and t1.type = '睡觉')
else
(select sum(t2.TOTAL_PRICE)
from FWLR_SJ t2
where t2.HTL_DD_ID = t.id
and t2.type = '睡觉')
end as zsFeeSum
from gp_dd t, jg d
where t.org_id = d.jcdm
order by orgName, supplierName, startTime desc

[ypopo1234]

这样确实不会少了，让你费心拉，速度没有改善，呵呵。没事。

[hebo2005]

贴贴你的执行计划,看看慢在哪里

[fibbery]

1、看看子查询能否转换成表之间的连接
2、使用nvl来替换你的case语句

[ypopo1234]

decode,nvl我都试过了，不能表达与它同样的功能出来，decode是不是不能嵌套的呢，嵌套就出错。

[hebo2005]

SELECT   DECODE (t.je1,
                 NULL, DECODE (t1.TYPE, '吃饭', t1.total_price, 0),
                 DECODE (t2.TYPE, '吃饭', t2.total_price, 0)
                ) cyfeesum,
         DECODE (t.je1,
                 NULL, DECODE (t1.TYPE, '睡觉', t1.total_price, 0),
                 DECODE (t2.TYPE, '睡觉', t2.total_price, 0)
                ) zsfeesum
    FROM gp_dd t, jg d, fwlr_yj t1, fwlr_sj t2
   WHERE t.org_id = d.jcdm AND t1.htl_dd_id = t.ID AND t2.htl_dd_id = t.ID
ORDER BY orgname, suppliername, starttime DESC

[hebo2005]

你这个子查询效率很低的，可以看下执行计划

[hebo2005]

忘记加sum了和group by 了

SELECT   sum(DECODE (t.je1,
                 NULL, DECODE (t1.TYPE, '吃饭', t1.total_price, 0),
                 DECODE (t2.TYPE, '吃饭', t2.total_price, 0)
                )) cyfeesum,
         sum(DECODE (t.je1,
                 NULL, DECODE (t1.TYPE, '睡觉', t1.total_price, 0),
                 DECODE (t2.TYPE, '睡觉', t2.total_price, 0)
                )) zsfeesum
    FROM gp_dd t, jg d, fwlr_yj t1, fwlr_sj t2
   WHERE t.org_id = d.jcdm AND t1.htl_dd_id = t.ID AND t2.htl_dd_id = t.ID
         group by t.id
ORDER BY orgname, suppliername, starttime DESC

[ypopo1234]

(select sum(t1.TOTAL_PRICE) from decode(t.je1,null,'FWLR_YJ','FWLR_SJ') as t1
where t1.HTL_DD_ID=t.id and t1.type = '吃饭') as cyFeeSum
为什么这样写不行哦！，decode不能这样用吗

[ypopo1234]

楼上的大哥，你辛苦了，承认你想的有点巧妙，比上面那位说用nvl的兄弟强了一点，不过，这样写肯定不能符合要求，为什么呢，因为你同时把
fwlr_yj t1, fwlr_sj t2两张表作了关联，这样查出来的结果就算正确，也会少一些记录了，不行

[hebo2005]

SELECT   sum(DECODE (t.je1,
                 NULL, DECODE (t1.TYPE, '吃饭', t1.total_price, 0),
                 DECODE (t2.TYPE, '吃饭', t2.total_price, 0)
                )) cyfeesum,
         sum(DECODE (t.je1,
                 NULL, DECODE (t1.TYPE, '睡觉', t1.total_price, 0),
                 DECODE (t2.TYPE, '睡觉', t2.total_price, 0)
                )) zsfeesum
    FROM gp_dd t, jg d, fwlr_yj t1, fwlr_sj t2
   WHERE t.org_id = d.jcdm AND t1.htl_dd_id(+) = t.ID AND t2.htl_dd_id(+) = t.ID
         group by t.id
ORDER BY orgname, suppliername, starttime DESC

这样应该不会少了，全部根据t.id