數(shù)據(jù)庫表腳本如下：

CREATE??TABLE?IF?NOT?EXISTS?user_kills?(
????id?INT?NOT?NULL?AUTO_INCREMENT,
????user_id?INT?NOT?NULL,
????user_name?VARCHAR(45)?NOT?NULL?,
????timestr?DATETIME?NOT?NULL,
????kills?INT?NOT?NULL?,
????PRIMARY?KEY(id)
)?DEFAULT?CHARACTER?SET?=?utf8;

數(shù)據(jù)庫數(shù)據(jù)腳本如下：

INSERT?INTO?`user_kills`?VALUES?(1,?3,?'孫悟空',?'2013-01-11?00:00:00',?20);
INSERT?INTO?`user_kills`?VALUES?(2,?4,?'沙僧',?'2013-01-10?00:00:00',?3);
INSERT?INTO?`user_kills`?VALUES?(3,?2,?'豬八戒',?'2013-01-10?00:00:00',?10);
INSERT?INTO?`user_kills`?VALUES?(4,?2,?'豬八戒',?'2013-02-01?00:00:00',?2);
INSERT?INTO?`user_kills`?VALUES?(5,?2,?'豬八戒',?'2013-02-05?00:00:00',?12);
INSERT?INTO?`user_kills`?VALUES?(6,?2,?'豬八戒',?'2013-02-06?00:00:00',?1);
INSERT?INTO?`user_kills`?VALUES?(7,?2,?'豬八戒',?'2013-02-07?00:00:00',?17);
INSERT?INTO?`user_kills`?VALUES?(8,?2,?'豬八戒',?'2013-02-11?00:00:00',?5);
INSERT?INTO?`user_kills`?VALUES?(9,?2,?'豬八戒',?'2013-02-12?00:00:00',?10);
INSERT?INTO?`user_kills`?VALUES?(10,?2,?'豬八戒',?'2013-01-10?00:00:02',?17);
INSERT?INTO?`user_kills`?VALUES?(11,?2,?'豬八戒',?'2013-02-01?00:00:01',?17);

執(zhí)行查詢語句如下：

SELECT?d.user_name,?c.timestr,?kills?FROM?(
????SELECT?user_id,?timestr,?kills,?
????????(SELECT?count(*)?FROM?user_kills?b?
????????????WHERE?b.user_id?=?a.user_id?AND?a.kills?<=?b.kills
????????)?AS?cnt
????FROM?user_kills?a?GROUP?BY?user_id,?timestr,?kills
)?c?JOIN?user1?d?ON?c.user_id?=?d.id?WHERE?cnt?<=?2;

結(jié)果如下：

結(jié)果查詢不出豬八戒的數(shù)據(jù)，因為豬八戒的最大殺怪?jǐn)?shù)有三條即以上，所以不顯示數(shù)據(jù)。