【mysql 分组取前几条】 先按某字段分组再取每组中前N条记录

先造数据
1、建表

CREATE TABLE `t2` ( `id` int(11) NOT NULL, `gid` char(1) DEFAULT NULL, `col1` int(11) DEFAULT NULL, `col2` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2、插入数据

insert into t2 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);

4、查询数据

SELECT id,gid,col1,col2 from t2 ORDER BY gid,col2 

1   A   31  6
21  A   89  9
26  A   87  13
31  A   83  49
16  A   32  67
36  A   39  75
11  A   2   78
6   A   29  97
22  B   15  22
17  B   84  38
37  B   22  39
27  B   40  45
7   B   88  63
12  B   30  79
2   B   25  83
32  B   4   90
33  C   81  7
18  C   27  9
3   C   76  21
8   C   16  22
38  C   76  67
13  C   96  73
23  C   46  84
28  C   34  90
29  D   63  8
39  D   20  11
34  D   11  12
19  D   31  21
14  D   37  40
9   D   25  43
4   D   63  56
24  D   54  79
35  E   85  10
5   E   3   17
10  E   45  28
40  E   81  36
30  E   66  40
20  E   80  63
25  E   85  64
15  E   14  86

3、取分组的第一条
方法1：

SELECT a.id,a.gid,a.col1,a.col2 FROM t2 a
LEFT JOIN t2 b
ON a.gid=b.gid AND a.col2>=b.col2
GROUP BY a.id,a.gid,a.col1,a.col2
HAVING COUNT(b.id)<=1
ORDER BY a.gid,a.col2 desc

1   A   31  6
22  B   15  22
33  C   81  7
29  D   63  8
35  E   85  10

方法2：

SELECT a.id,a.gid,a.col1,a.col2 FROM t2 a WHERE 1>=( SELECT COUNT(*) FROM t2 b WHERE a.gid=b.gid AND a.col2>=b.col2) ORDER BY a.gid,a.col2 desc 

1   A   31  6
22  B   15  22
33  C   81  7
29  D   63  8
35  E   85  10

4、取分组的最后一条
方法1：

SELECT a.id,a.gid,a.col1,a.col2 FROM t2 a
LEFT JOIN t2 b
ON a.gid=b.gid AND a.col2<=b.col2
GROUP BY a.id,a.gid,a.col1,a.col2
HAVING COUNT(b.id)<=1
ORDER BY a.gid,a.col2 desc

6   A   29  97
32  B   4   90
28  C   34  90
24  D   54  79
15  E   14  86

方法2：

SELECT a.id,a.gid,a.col1,a.col2 FROM t2 a WHERE 1>=( SELECT COUNT(*) FROM t2 b WHERE a.gid=b.gid AND a.col2<=b.col2) ORDER BY a.gid,a.col2 desc

6   A   29  97
32  B   4   90
28  C   34  90
24  D   54  79
15  E   14  86