以下语句执行下来需要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
三个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
2、使用nvl来替换你的case语句
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
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
where t1.HTL_DD_ID=t.id and t1.type = '吃饭') as cyFeeSum
为什么这样写不行哦!,decode不能这样用吗
fwlr_yj t1, fwlr_sj t2两张表作了关联,这样查出来的结果就算正确,也会少一些记录了,不行
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