MySQL取每组的前N条记录

时间：2021-08-09 15:08:14

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

 1 1.用子查询：
 2 SELECT * FROM right2 a  WHERE 2>
 3 (SELECT COUNT(*) FROM right2 b WHERE b.id=a.id AND b.account>a.account)
 4 ORDER BY a.id,a.account DESC
 5 2.用exists半连接：
 6 SELECT * FROM right2 a  WHERE EXISTS
 7 (SELECT COUNT(*) FROM right2 b WHERE b.id=a.id AND a.account<b.account HAVING COUNT(*)<2)
 8 ORDER BY a.id,a.account DESC
 9 同理可以取组内最小的N条记录：
10 SELECT * FROM right2 a  WHERE 2>
11 (SELECT COUNT(*) FROM right2 b WHERE b.id=a.id AND b.account<a.account)
12 ORDER BY a.id,a.account DESC
13 用exists：
14 SELECT * FROM right2 a  WHERE EXISTS
15 (SELECT COUNT(*) FROM right2 b WHERE b.id=a.id AND a.account>b.account HAVING COUNT(*)<2)
16 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条

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

 

标签：

相关文章