使用纯SQL很难搞定吧。不过可以试试下面的SQL语句:UPDATE ent e SET LEVEL =
(SELECT LEVEL FROM ent_history eh WHERE eh.code = e.code AND eh.date =
(SELECT MAX(DATE) FROM ent_history eh1 WHERE eh1.code = eh.code)
)
WHERE LEVEL IS NULL;
上面这个语句的执行效率会相当低。不过用PL/SQL写个存储过程到是很容易搞定。
(SELECT LEVEL FROM ent_history eh WHERE eh.code = e.code AND eh.date =
(SELECT MAX(DATE) FROM ent_history eh1 WHERE eh1.code = eh.code)
)
WHERE LEVEL IS NULL;
上面这个语句的执行效率会相当低。不过用PL/SQL写个存储过程到是很容易搞定。
(SELECT LEVEL FROM ent_history eh WHERE eh.code = e.code AND eh.date =
(SELECT MAX(DATE) FROM ent_history eh1 WHERE eh1.code = eh.code)
)
WHERE LEVEL IS NULL;
上面这个语句的执行效率会相当低。不过用PL/SQL写个存储过程到是很容易搞定。
--his表倒序取第一条
SELECT CODE, LEVEL, DATE
FROM (SELECT CODE,
LEVEL,
DATE,
ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY DATE DESC) ROW_
FROM ENT_HISTORY)
WHERE ROW_ = 1;--更新ent表数据,最好用merge into
MERGE INTO ENT A
USING (SELECT CODE, LEVEL, DATE
FROM (SELECT CODE,
LEVEL,
DATE,
ROW_NUMBER() OVER(PARTITION BY CODE ORDER BY DATE DESC) ROW_
FROM ENT_HISTORY)
WHERE ROW_ = 1) B
ON A.CODE = B.CODE AND A.LEVEL IS NULL
WHEN MATCHED THEN
UPDATE SET A.LEVEL = B.LEVEL;
UPDATE ent e SET level =
(SELECT level FROM ent_history eh WHERE eh.code = e.code AND ROWNUM = 1 ORDER BY eh.date DESC )
WHERE level IS NULL;
UPDATE ent e SET level =
(SELECT level FROM ent_history eh WHERE eh.code = e.code AND ROWNUM = 1 ORDER BY eh.date DESC )
WHERE level IS NULL;
UPDATE ent e SET level =
(SELECT level FROM ent_history eh WHERE eh.code = e.code AND ROWNUM = 1 ORDER BY eh.date DESC )
WHERE level IS NULL;不好意思,尝试发源代码。
update ent a set level =
(select level from ent_history b where a.code = b.code and rownum = 1 order by b.date desc )
where level is null;
--如果要将c条记录也插入,可以用merge语句
merge into ent a
using (select b.code, b.level
from ent_history b,(select code, max(date) as date from ent_history group by code) c
where b.code=c.code and b.date=c.date
) d
on (a.code = b.code)
when matched then
update set a.code = d.code, a.level = d.level;
when not matched then
insert into(a.code, a.level) values(d.code, d.level);