mysql 分组取最新n回复_MySql实现分组取n条最大记录

MySql实现分组取n条最大记录.

有这么一张表，要求根据resid分组取pscore,sscore最大的2条记录，如果pscore相等，那么根据sscore进行判断，如果sscore也想等，那么可以随意取

resid pscore sscore id

------ ------ ------ --------------------------------

aaa 100.00 5.00 f0e8436c0a2b11e7b2bc0021ccba30b6

aaa 100.00 5.00 f0e850660a2b11e7b2bc0021ccba30b6

aaa 100.00 5.00 f0e84c340a2b11e7b2bc0021ccba30b6

aaa 100.00 5.00 f0e847450a2b11e7b2bc0021ccba30b6

aaa 100.00 4.00 f0e83caf0a2b11e7b2bc0021ccba30b6

aaa 98.00 5.00 f0e8443a0a2b11e7b2bc0021ccba30b6

aaa 96.00 5.00 f0e8464d0a2b11e7b2bc0021ccba30b6

bbb 100.00 5.00 f0e845c80a2b11e7b2bc0021ccba30b6

bbb 100.00 5.00 f0e852410a2b11e7b2bc0021ccba30b6

bbb 98.00 1.00 f0e845480a2b11e7b2bc0021ccba30b6

bbb 96.00 3.50 f0e846c90a2b11e7b2bc0021ccba30b6

bbb 96.00 2.00 f0e844c30a2b11e7b2bc0021ccba30b6

对于重复的记录可以根据rownum设置一个num值这样就可以获得准确的记录数

对应sql：

SELECT *

FROM (SELECT

@r:=@r+1 rnum, ss.*

FROM (SELECT * FROM xx ORDER BY xx.resid ASC,xx.pscore DESC,xx.sscore DESC) ss,(SELECT @r:=0) r) a

WHERE (SELECT

COUNT(b.id)

FROM (SELECT @r:=@r+1 rnum,ss.* FROM (SELECT * FROM xx ORDER BY xx.resid ASC,xx.pscore DESC,xx.sscore DESC) ss,(SELECT @r:=0) r) b

WHERE a.resid = b.resid

AND b.rnum &lt a.rnum) &lt 2