具体代码为:
select bc2.nomb,
(case when getdate(bc2.putdate,bc2.matdate) ='01' then
(select ri.TeamRate
from rate_info ri
where ri.rateyear>= replace(bc2.putoutdate,'/','-')
and ri.teamtime ='1'
and rownum=1
order by ri.rateyear asc)
else 0 end
) businessrate
from bc2,
lc2
where bc2.nomb = lc2.nomb
and bc2.nomb not in(select bd2.no2 from bd2)
and bc2.putdate is not null
and bc2.matdate is not null; 而下面语句确可以正确执行:select ri.TeamRate
from rate_info ri
where ri.rateyear>= replace(bc2.putoutdate,'/','-')
and ri.teamtime ='1'
and rownum=1
order by ri.rateyear asc我子查询的主要目的是查找大于某个日期的最大利率还有没有什么好的方法可以改进这个子查询,能达到我要求的目标。
select bc2.nomb,
(case when getdate(bc2.putdate,bc2.matdate) ='01' then
(select ri.TeamRate
from rate_info ri
where ri.rateyear>= replace(bc2.putoutdate,'/','-')
and ri.teamtime ='1'
and rownum=1
order by ri.rateyear asc)
else 0 end
) businessrate
from bc2,
lc2
where bc2.nomb = lc2.nomb
and bc2.nomb not in(select bd2.no2 from bd2)
and bc2.putdate is not null
and bc2.matdate is not null; 而下面语句确可以正确执行:select ri.TeamRate
from rate_info ri
where ri.rateyear>= replace(bc2.putoutdate,'/','-')
and ri.teamtime ='1'
and rownum=1
order by ri.rateyear asc我子查询的主要目的是查找大于某个日期的最大利率还有没有什么好的方法可以改进这个子查询,能达到我要求的目标。
解决方案 »
- 求解: ORA-12154:TNS:监听程序当前无法解析指定的连接描述标符中请求的服务
- checkpoint点、心跳与commit之间的联系?
- 这个SQL该怎么写?
- 部分行列转换问题,请大家帮忙提提建议;
- 急,在线等 合并连续记录
- 问题解决在给300分
- 如何把数据库生成的脚本".sql"文件通过sql plus 导入到oracle指定的用户下
- 导出导入备份的问题。(Waiting online)
- SELECT * from jm WHERE jmcsny=TO_DATE( '1978-03-01','YYYY-MM-DD')哪里有错?再线等待
- 傻透了,一时糊涂我把oracle的用户删掉了,怎么恢复数据?,大家帮我啊!!
- 关于where条件中的条件判断,麻烦各位大哥看下
- 面试 超难的sql语句
select bc2.nomb,
(case when getdate(bc2.putdate,bc2.matdate) ='01' then
(select ri.TeamRate
from rate_info ri
where ri.rateyear>= replace(bc2.putoutdate,'/','-')
and ri.teamtime ='1'
and rownum=1
order by ri.rateyear asc group by ri.rateyear )
else 0 end
) businessrate
from bc2,
lc2
where bc2.nomb = lc2.nomb
and bc2.nomb not in(select bd2.no2 from bd2)
and bc2.putdate is not null
and bc2.matdate is not null;
from rate_info ri
where ri.rateyear>= replace(bc2.putoutdate,'/','-')
and ri.teamtime ='1'
and rownum=1
order by ri.rateyear asc 此句查询得到的不是最大值吧
select teamrate
from(
select ri.TeamRate
from rate_info ri
where ri.rateyear>= replace(bc2.putoutdate,'/','-')
and ri.teamtime ='1'
order by ri.rateyear asc
)
where rownum=1 这样才是对的