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

来源：互联网发布：网络订餐江苏食药监编辑：程序博客网时间：2024/05/17 22:31

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

0 0