以下语句执行下来需要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
解决方案 »
- oracle 表空间和用户的问题
- 4G内存安装Oracle 11g 可以不设交换区吗?
- 学校图书馆关系系统中学生和管理员 在Oracle中的“身份”问题
- oracle pl/sql 课本推荐
- 救死,,,,存储过程
- 送出50分只为得到一个合理解析,请高手帮忙
- 《续一》各位高手帮我分析几道题,据说是某个公司的笔试题目。(感激!回复既有分数)
- 急!分区时,是一个表空间好还是几个表空间好?
- 优化sql语句,在线等,多谢!
- 用imp导入记录时,如果目的表存在主键相同但是其它字段内容不同的记录,如果此时我希望用源记录覆盖目的记录,请教参数是什么、imp命令该
- 写了个SQL自动生成工具,以提高开发数据库的效率,有兴趣的朋友欢迎来使用
- 关于求平均的问题
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