mysql分组排序取前N条记录的最简洁的单条sql !

-- mysql分组排序取前N条记录的最简洁的单条sql。

use test;

drop table if exists test;

create table test (

id int primary key,

cid int,

author varchar(30)

) engine=myisam;

insert into test values

(1,1,\'test1\'),

(2,1,\'test1\'),

(3,1,\'test2\'),

(4,1,\'test2\'),

(5,1,\'test2\'),

(6,1,\'test3\'),

(7,1,\'test3\'),

(8,1,\'test3\'),

(9,1,\'test3\'),

(10,2,\'test11\'),

(11,2,\'test11\'),

(12,2,\'test22\'),

(13,2,\'test22\'),

(14,2,\'test22\'),

(15,2,\'test33\'),

(16,2,\'test33\'),

(17,2,\'test33\'),

(18,2,\'test33\');

--

select * from (select cid,author,count(*) as number from test group by cid,author) a

where

2>(

select count(*)

from (select cid,author,count(*) as number from test group by cid,author) b

where a.cid=b.cid and a.number<b.number

)order by cid,number desc;