mysql 全局排序 取第n条_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

)order by cid,number desc;