在使用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 每次增加的时候,向队列里面插入数据,同时删除老的数据。