sql max 问题 | Code Bye

mysql 表usercount数据
userID       count         publicDate
49              101            2014-5-6
50               103           2014-5-9
50              108            2014-6-3
CREATE OR REPLACE VIEW usercount_view AS
SELECT userID,count,MAX(publicDate) FROM usercount GROUP BY userID;
本想要的是这个结果
49              101            2014-5-6
50              108            2014-6-3
但却是这个结果
49              101            2014-5-6
50              103            2014-6-3
请高手帮忙修改一下SQL语句，叩谢！

解决方案

CREATE OR REPLACE VIEW usercount_view AS
select userID,count,publicDate
from usercount u,(SELECT userID,MAX(publicDate) max_publicDate FROM usercount GROUP BY userID) g
where u.userID = g.userID
and u.publicDate =g.max_publicDate

引用 1 楼 u011575570 的回复:

CREATE OR REPLACE VIEW usercount_view AS
select userID,count,publicDate
from usercount u,(SELECT userID,MAX(publicDate) max_publicDate FROM usercount GROUP BY userID) g
where u.userID = g.userID
and u.publicDate =g.max_publicDate

group by取的是第一个数据，所以不会去判断数据的一致性。

select a.* from usercount a where not exists (select 1 from usercount b where a.userId=b.userId and a.publicDate < b.publicDate)

参考下贴中的多种方法
http://blog.csdn.net/acmain_chm/article/details/4126306
[征集]分组取最大N条记录方法征集，及散分….