update memberinfo
Set start = (select max(endstar) from updatelog where updatelog.memberid=memberinfo.memberid)
where exists
(select 1 from updatelog where updatelog.memberid=memberinfo.memberid and rownum = 1)上面语句考虑memberinfo和updatelog是一对多的关系。
调试欢乐多
update memberinfo a set star = (select max(endstar) from updatelog b where a.memberid = b.memberid ) where exists(select 1 from updatelog where a.memberid = b.memberid)
比如一个数据memberid = 001, star = 1,那么在updatelog中有memberid=001,endstar=2以及memberid=001,endstar=3两条记录,那么我想得到的纪录为memberid=001,star=3
memberid star
001 1
002 1
003 2
updatelog数据如下
memberid endstar updatetime
001 2 2005-5-24
001 3 2005-5-28
001 5 2005-6-9
002 4 2005-5-29那么我想得到的数据为
会员号 星级
001 5
002 4
003 2请问怎么用一条SQL语句实现?
SELECT memberid AS 会员号, CASE WHEN
((SELECT TOP 1 endstar
FROM updatelog
WHERE memberinfo.memberid = updatelog.memberid
ORDER BY updatetime DESC) IS NULL) THEN memberinfo.star ELSE
(SELECT TOP 1 endstar
FROM updatelog
WHERE memberinfo.memberid = updatelog.memberid
ORDER BY updatetime DESC) END AS 星级
FROM dbo.memberinfo