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
