sql*plus下呢?
update bfrbhz set sjkfc=(select a.sjkfc
(select sjkfc from bfrb where RQ between '2003-01-01' and '2003-12-31' and bf=bfrbhz.bf order by rq desc) a
where rownum=1);
update bfrbhz set sjkfc=(select a.sjkfc
(select sjkfc from bfrb where RQ between '2003-01-01' and '2003-12-31' and bf=bfrbhz.bf order by rq desc) a
where rownum=1);
解决方案 »
- 数据导入错误:IMP-00017: following statement failed with ORACLE error 2299 请高手指点迷津
- oracle语句
- ora-01406读取的列值被截断
- grouping sets为何出错
- oracle连接外网问题。
- 如何把存储过程中的select赋给别人???
- Asp.NET + Oracle 出现下面的错误,指定的参数已超出有效值的范围。参数名: 小时、分和秒参数描述无法表示的 DateTime。请各位大侠出手。
- 关于用VB设置oracle的ODBC及操作
- 利用toad如何编辑查询结果?
- 数据库的备份(back exp imp)急急急.......
- varchar2类型在Oracle中理论上可以存储4000bytes的字节 , 但是我在某数据库中设定一个可以存储4000字节的字段, 在插入3千多个字节的时候
- 求救。关于12560错误
SQL> select 1 from dual where trunc(sysdate)='2003-07-30';
select 1 from dual where trunc(sysdate)='2003-07-30'
*
ERROR 位于第 1 行:
ORA-01861: 文字与格式字符串不匹配
已用时间: 00: 00: 00.30
SQL> select 1 from dual where trunc(sysdate)='30-7月 -03'; 1
----------
1已用时间: 00: 00: 00.40
SQL> alter session set nls_date_format='yyyy-mm-dd';会话已更改。已用时间: 00: 00: 00.20
SQL> select 1 from dual where trunc(sysdate)='2003-07-30'; 1
----------
1已用时间: 00: 00: 00.80
SQL>
SQL> select 1 from dual where trunc(sysdate)='2003-07-30';
select 1 from dual where trunc(sysdate)='2003-07-30'
*
ERROR 位于第 1 行:
ORA-01861: 文字与格式字符串不匹配
已用时间: 00: 00: 00.30
SQL> select 1 from dual where trunc(sysdate)='30-7月 -03'; 1
----------
1已用时间: 00: 00: 00.40
SQL> alter session set nls_date_format='yyyy-mm-dd';会话已更改。已用时间: 00: 00: 00.20
SQL> select 1 from dual where trunc(sysdate)='2003-07-30'; 1
----------
1已用时间: 00: 00: 00.80
SQL>
to bzszp(SongZip) :是不是应该是....sjkfc=(select a.sjkfc from .....
您好像少了一个from
如果改成这样,toad提示:ORA-00904: 无效列名
似乎ORACLE在此种情况下不允许用order by
不过就不能用rownum=1来取最大的一条了
这样就只能通过 rq =( select max(rq) from tbname where ...)麻烦~
update bfrbhz set sjkfc=(select nvl(sjkfc,0) from bfrb where rq=(select max(rq) from bfrb where trim(RQ) between '2003-01-01' and '2003-12-31' and trim(bf)=bfrbhz.bf and rownum=1) and trim(bf)=bfrbhz.bf)
但我觉得,在SQL Server很轻松实现的,但在ORACLE就得这么费周折?
update bfrbhz set sjkfc=(select a.sjkfc
(select sjkfc,bf from bfrb where RQ between '2003-01-01' and '2003-12-31' order by rq desc) a where rownum=1 and a.bf=bfrbhz.bf);
或者
update bfrbhz set sjkfc=(select a.sjkfc from (select bf,sjkfc,rank() over(order by rq desc) rk from bfrb where RQ between '2003-01-01' and '2003-12-31') a where a.bf=bfrbhz.bf and rk=1)
这样两层子查询的话,会提示,无效列名的
8i
8.1.5
where a.bf=b.bf and
to_char(to_date(a.rq,'yyyymmdd'),'yyyymmdd') between '20030101' and '20031231' and
to_char(to_date(a.rq,'yyyymmdd'),'yyyymmdd')= (select to_char(to_date(max(rq),'yyyymmdd'),'yyyymmdd') from bfrb);
update bfrbhz set sjkfc=(select a.sjkfc from
(select sjkfc,bf from bfrb where RQ between '2003-01-01' and '2003-12-31' order by rq desc) a where rownum=1 and a.bf=bfrbhz.bf);
或者
update bfrbhz set sjkfc=(select a.sjkfc from (select bf,sjkfc,rank() over(order by rq desc) rk from bfrb where RQ between '2003-01-01' and '2003-12-31') a where a.bf=bfrbhz.bf and a.rk=1)
第一个OK,
第二个:ORA-00923: 未找到预期 FROM 关键字