MySQL取每组的前N条记录

一、对分组的记录取前N条记录：例子：取前 2条最大(小)的记录

1.用子查询：
SELECT * FROM right2 a  WHERE 2>
(SELECT COUNT(*) FROM right2 b WHERE b.id=a.id AND b.account>a.account)
ORDER BY a.id,a.account DESC
2.用exists半连接：
SELECT * FROM right2 a  WHERE EXISTS
(SELECT COUNT(*) FROM right2 b WHERE b.id=a.id AND a.account<b.account HAVING COUNT(*)<2)
ORDER BY a.id,a.account DESC
同理可以取组内最小的N条记录：
SELECT * FROM right2 a  WHERE 2>
(SELECT COUNT(*) FROM right2 b WHERE b.id=a.id AND b.account<a.account)
ORDER BY a.id,a.account DESC
用exists：
SELECT * FROM right2 a  WHERE EXISTS
(SELECT COUNT(*) FROM right2 b WHERE b.id=a.id AND a.account>b.account HAVING COUNT(*)<2)
ORDER BY a.id,a.account DESC

 如果取每组的最大(小)一条记录我常用：

select t1.* from table t inner join(select * from table t1 order by id desc) t1 on t.id=t1.id group by t.id order by t.id; //一定用t1.*,用t.*不OK

二.实例：取每组最大的前 N条

create table t2 (
  id int primary key,       
  gid char, 
  col1 int, 
  col2 int  
) engine=innodb; 
insert into tx01 values
(1,'A',31,6), 
(2,'B',25,83), 
(3,'C',76,21), 
(4,'D',63,56), 
(5,'E',3,17), 
(6,'A',29,97), 
(7,'B',88,63), 
(8,'C',16,22), 
(9,'D',25,43), 
(10,'E',45,28), 
(11,'A',2,78), 
(12,'B',30,79), 
(13,'C',96,73), 
(14,'D',37,40), 
(15,'E',14,86), 
(16,'A',32,67), 
(17,'B',84,38), 
(18,'C',27,9), 
(19,'D',31,21), 
(20,'E',80,63), 
(21,'A',89,9), 
(22,'B',15,22), 
(23,'C',46,84), 
(24,'D',54,79), 
(25,'E',85,64), 
(26,'A',87,13), 
(27,'B',40,45), 
(28,'C',34,90), 
(29,'D',63,8), 
(30,'E',66,40), 
(31,'A',83,49), 
(32,'B',4,90), 
(33,'C',81,7), 
(34,'D',11,12), 
(35,'E',85,10), 
(36,'A',39,75), 
(37,'B',22,39), 
(38,'C',76,67), 
(39,'D',20,11), 
(40,'E',81,36); 
create table tx01 (
  id int primary key, 
  gid char, 
  col1 int, 
  col2 int  
) engine=innodb;

取每组gid 最大的前N条记录：使用自连接或则半连接：

*N=1时：

自连接：降序排好后group by取每组最大的一条。

select * from (select * from t2 order by col2 desc)as a group by gid order by gid;

半连接方式：找不到比最大值还大的。

select * from t2 a where not exists(select 1 from t2 b where b.gid=a.gid and b.col2>a.col2) order by a.gid; 

*N=3时：

自连接：

select * from t2 a where 3>(select count(*) from t2 where gid=a.gid and col2>a.col2) order by a.gid,a.col2 desc;

半连接：

select * from t2 a where exists(select count(*) from t2 b where b.gid=a.gid and a.col2<b.col2 having(count(*))<3) order by a.gid,a.col2 desc

转：本文出自 http://huanghualiang.blog.51cto.com/6782683/1252630