sql max 问题

[xiyaoponewnew]

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语句，叩谢！！！

[rick-he]

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

[rick-he]

引用 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取的是第一个数据，所以不会去判断数据的一致性。

[benluobo]

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

[ACMAIN_CHM]

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