MySql 按某一字段分组，同时取每组的前三行数据

-- 按gid分组后，查询col2的前三名

-- 方法一

SELECT a.* 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)<=3

ORDER BY a.gid,a.col2 desc;

-- 方法二

select a.id,a.gid, a.col1, a.col2

from

(

select a.*, if(@tmpgid = a.gid, @rank := @rank+1, @rank := 0) as rank, @tmpgid := a.gid, @number := @number+1, @rank, @tmpgid

from

(select * from t2 order by gid, col2 desc) a, (select @rank :=0, @number :=0, @tmpgid := '') b) a

where rank < 3;

-- 建表

create table t2 (

id int primary key,

gid char,

col1 int,

col2 int

) engine=myisam;

-- 初始化数据

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);