最大值----1

能用一条语句实现用分值最大的记录去更新某一列

我们经常碰到这样的问题，表A有字段xh,xm,km，一个xh只有一条记录，表B有字段xh，km，score，一个学号有多条记录。

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);

[@more@]

两个方法：(最大科目)
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博客 ” ，链接：http://blog.itpub.net/16115580/viewspace-1021627/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/16115580/viewspace-1021627/