MySQL取每组的前N条记录

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

# 1.用子查询：
SELECT * FROM right2 AS a
WHERE 2 > (
	SELECT COUNT(*) FROM right2 AS b
	WHERE b.id = a.id AND b.account > a.account
)
ORDER BY a.id, a.account DESC
# 2.用exists半连接：
SELECT * FROM right2 AS a 
WHERE EXISTS (
	SELECT COUNT(*) FROM right2 AS 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 AS a
WHERE 2 > (
	SELECT COUNT(*) FROM right2 AS b
	WHERE b.id = a.id AND b.account < a.account
)
ORDER BY a.id, a.account DESC
# 用exists：
SELECT * FROM right2 AS a
WHERE EXISTS (
	SELECT COUNT(*) FROM right2 AS b
	WHERE b.id = a.id AND a.account > b.account HAVING COUNT(*) < 2
)
ORDER BY a.id, a.account DESC
# SQLServer支持top-N：
select a.* from tb a where val = (
	select top 3 val from tb where name = a.name
) order by a.name

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

select id, val from t AS b
inner join (
	select * from t AS a
	where order by val desc
) a on a.id = b.id
group by a.id order by id;

二.实例：取每组最大的前 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 as a
where not exists (
	select 1 from t2 AS b
	where b.gid = a.gid and b.col2 > a.col2
) order by a.gid;

*N=3时：

• 自连接

select * from t2 AS 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 AS a
where exists (
	select count(*) from t2 AS b
	where b.gid = a.gid and a.col2 < b.col2 having(count(*)) < 3
) order by a.gid,a.col2 desc

转自51CTO博客作者smart818的原创作品

· MySQL取每组的前N条记录