最大值----1
xh=学号,xm=姓名,km=科目,score=分数。目的：取得学生成绩最好的一门科目名称
现在，要根据xh取B表中最大的score的记录的km，去更新A表的km。
update A
set A.km = (select B.km
from B
where B.xh = A.xh
and rownum = 1
order by B.score desc);
稍微有点常识的朋友都会知道上面的语句是存在问题的。上面取出来的km并不是score最大的那条记录的km。
但是，这样写也是不行的。
update A
set A.km = (select km
from (select km
from B
where B.xh = A.xh
order by B.score desc)
where rownum = 1);
两个方法：(最大科目)
update A set A.km = (select MAX(B.km) KEEP(DENSE_RANK LAST ORDER BY b.score) FROM b where B.xh = A.xh);
MERGE INTO A
USING (SELECT b.xh
,MAX(b.km) KEEP(DENSE_RANK LAST ORDER BY b.score) AS km
FROM b
GROUP BY b.xh
) b
ON (a.xh=b.xh)
WHEN MATCHED THEN UPDATE
SET a.km = b.km;
最大成绩的科目:
update a
set a.km=(select b.km from (select b.*,row_number()over(partition by b.xh order by b.score desc) rn from b) b
where b.rn=1 and a.xh=b.xh)
where exists (select 1 from from (select b.*,row_number()over(partition by b.xh order by b.score desc) rn from b) b
where b.rn=1 and a.xh=b.xh)
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/16115580/viewspace-1021627/ ，如需转载，请注明出处，否则将追究法律责任。
