有二张表 hzb 和 lsz
hzb 中字段有 zh,ye (主键为zh)每个记录是唯一的
lsz 中字段有 zh,jyr,ye 记录不唯一
hzb表
zh ye
223 2000.00
224 1002.23
221 1001.00
lsz表
zh ye jyr(交易时间)
223 100 200809110823
223 50 200809110924
223 10 200809110531
221 100 200809120512
221 1000 200809140648
想把表hzb中ye字段值(根据两表帐号相等)更新为表lsz中jyr 最大的ye值,表lsz 中没有的zh 在hzb 表中不做ye处理保持不变 结果变为
zh ye
223 50
221 1000
224 1002.23如何用语句实现,这两个表中记录都好几十万条要求一条执行高效点的.
hzb 中字段有 zh,ye (主键为zh)每个记录是唯一的
lsz 中字段有 zh,jyr,ye 记录不唯一
hzb表
zh ye
223 2000.00
224 1002.23
221 1001.00
lsz表
zh ye jyr(交易时间)
223 100 200809110823
223 50 200809110924
223 10 200809110531
221 100 200809120512
221 1000 200809140648
想把表hzb中ye字段值(根据两表帐号相等)更新为表lsz中jyr 最大的ye值,表lsz 中没有的zh 在hzb 表中不做ye处理保持不变 结果变为
zh ye
223 50
221 1000
224 1002.23如何用语句实现,这两个表中记录都好几十万条要求一条执行高效点的.
from
hzb,
(select zh,ye,max(jyr) from lsz)
where hzb.zh=lsz.zh
update hzb
set ye=
select ye
from
(
(select zh,ye
row_number() over (partition by zh order by jyr desc) jj
from lsz)
) hh
where jj=1 and hzb.zh=hh.zh;
大致是这样的吧
set ye=(select max(ye) from lsz where hzb.zh=hh.zh)
where
exists
(select 1 from lsz where hzb.zh=hh.zh)
set ye = (select ye
from ((select zh,
ye,
row_number() over(partition by zh order by jyr desc) jj
from lsz)) hh
where jj = 1
and hzb.zh = hh.zh)
where exists (select 1 from lsz where lsz.zh = hzb.zh)
set aaa.ye =
(select aa.ye
from lsz aa
where aa.jyr = (
select max(bb.jyr)
from lsz bb
group by bb.zh))
where aaa.zh = aa.zh
我在SQL_plus 下运行提示:
ERROR 位于第 1 行:
ORA-00439: 未启用特征: OLAP Window Functions
我想在SQL>下执行
(select a.zh,b.ye,b.jyr from zh a full outer join lsz b on(a.zh=b.zh))c
group by c.zh
怎么没有update 字样呀?
(select c.zh,max(jyr) from
(select a.zh,b.ye,b.jyr from zh a full outer join lsz b on(a.zh=b.zh))c
group by c.zh)d full outer join zh e on(d.zh=e.zh)
(select ye from(select ye from lsz where zh=hzb.zh order by jyr desc) where rownum=1))
set a.ye = (select b.ye
from (select row_number() over(partition by zh order by jyr desc) as rn,
zh,
ye
from lsz) b where a.zh = b.zh and b.rn = 1)
where exists (select 1
from (select row_number() over(partition by zh order by jyr desc) as rn,
zh,
ye
from lsz) b where a.zh = b.zh and b.rn = 1)
SP2-0734: 未知的命令开头 "rownum=1))..." - 忽略了剩余的行。
请用下面的语句试下
update hzb set ye=(select ye from(select ye from lsz where zh=hzb.zh order by jyr desc) where rownum=1) where exists(select 1 from lsz where hzb.zh=lsz.zh)
*
ERROR 位于第 1 行:
ORA-00904: 无效列名 检查过了表里列名肯定没有问题.