SQL数据聚合问题
有这样一个表：
UserState
ID   UserID     UserState     AddTime
1         23             上岗             2007-3-21
2         23             辞职             2007-3-25
3         24             等待中         2007-3-1
4         24             面试             2007-3-21

现在要求显示所有用户的最近的状态列表，SQL语句改怎么写呢？就是最终显示成这样的：
ID   UserID   UserState     AddTime
2         23           辞职             2007-3-25
4         24           面试             2007-3-21

------解决方案--------------------
create table UserState
(ID int, UserID int, UserState varchar(50), AddTime datetime)
insert into UserState select 1, 23, '上岗 ', '2007-3-21 '
union all select 2, 23, '辞职 ', '2007-3-25 '
union all select 3, 24, '等待中 ', '2007-3-1 '
union all select 4, 24, '面试 ', '2007-3-21 '


select * from userstate where id in(select max(id) from userstate group by userid)
------解决方案--------------------
select A.* from UserState A inner join (select UserID,max(AddTime) AddTime from UserState group by UserID)B on A.UserID = B.UserID and A.AddTime = B.AddTime
------解决方案--------------------
select * from userstate where id in(select max(id) from userstate group by userid)
正解
------解决方案--------------------
我上面关联条件写错了

select * from UserState
where not exists(select 1 from UserState a where a.userid=UserState.userid and a.addtime> UserState.addtime)

------解决方案--------------------
select tb.* from tb join (select userid ,max(isnull(addtime,0)) newdate from tb group by userid ) t
on tb.userid=t.userid and tb.addtime=t.newdate