mysql中查询每个分组的前几名或者分组取最大N条记录的方法探讨

在使用SQL的过程中,我们经常遇到这样一类问题:如何找出每个程序最近的日志条目?如何找出每个用户的最高分?在每个分类中最受欢迎的商品是什么?通常这类“找出每个分组中最高分的条目”的问题可以使用相同的技术来解决

我们以如下例子来测试:

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


方法1 union all:

(select * from t2 where type = 'A' order by col2 limit 3) union all (select * from t2 where type = 'B' order by col2 limit 3) union all (select * from t2 where type = 'C' order by col2 limit 3) union all (select * from t2 where type = 'D' order by col2 limit 3)

方法2:

set @x = 0;
set @g = 'A';
SELECT * FROM (SELECT @x:=IF(@g = t2.gid, @x+1, IF(@g:=t2.gid, @x, 1)) AS s, t2.* FROM t2 ORDER BY gid ASC, col2 DESC) AS t WHERE s <= 3;

方法3:

SELECT
    a.*

FROM
    t2 a,t2 b
WHERE
    a.gid = b.gid AND a.col2 < b.col2
GROUP BY
    a.id, a.gid, a.col1, a.col2
HAVING  COUNT(a.id) <= 3 ORDER BY   a.gid, a.col2 DESC

方法4和3类似:

SELECT a.id,a.gid,a.col1,a.col2 FROM t2v a
LEFT JOIN t2v 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 t2v a
WHERE 3>=(
SELECT COUNT(*) FROM t2v b
WHERE a.gid=b.gid AND a.col2<=b.col2)
ORDER BY a.gid,a.col2 desc


SELECT a.id,a.gid,a.col1,a.col2 
FROM t2  a,t2 b 
where 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


方法基本上都是类似,但是在大数据的时候效率就凸显出来了。

个人建议,可以采用队列的方式。

gid 每次增加的时候,向队列里面插入数据,同时删除老的数据。


关键词: 分组取值 , 分组取最大

上一篇: Mysql DBA系统学习(2)了解mysql的源码目录及源文件
下一篇: mysql分表技术之利用MRG_MyISAM存储引擎分表法

目前还没有人评论,您发表点看法?
发表评论

评论内容 (必填):