create table t (
id int primary key,
gid char,
col1 int,
col2 int
) ;
添加800W条记录:
DELIMITER $$
DROP PROCEDURE IF EXISTS addData $$
CREATE DEFINER=`root`@`192.168.4.32` PROCEDURE `c`()
begin
set @i = 8000000;
while(@i > 0)
do
set @c = rand() * 5 + 1;
insert into t(gid,col1,col2) values(substr('ABCDE', @c, 1), rand() * 100, rand() * 100);
set @i = @i - 1;
end while;
end$$
DELIMITER ;
call addDate();
drop addData;
分组获取:
这里获取每组中前3条col2最高的记录
select t.* from t join
(select left(group_concat(LPAD(id,10,'0') order by col2 desc,id desc), 11*3 - 1) ids, gid
from t group by gid) a
on find_in_set(LPAD(id,10,'0'), ids) and t.gid=a.gid order by gid;
800W条记录执行了20多秒
+---------+-----+------+------+
| id | gid | col1 | col2 |
+---------+-----+------+------+
| 7994579 | A | 82 | 100 |
| 7995601 | A | 23 | 100 |
| 7996919 | A | 25 | 100 |
| 7995333 | B | 94 | 100 |
| 7997657 | B | 39 | 100 |
| 7998573 | B | 67 | 100 |
| 7999288 | C | 47 | 100 |
| 7999360 | C | 68 | 100 |
| 7999809 | C | 85 | 100 |
| 7998444 | D | 100 | 100 |
| 7999071 | D | 79 | 100 |
| 7999555 | D | 30 | 100 |
| 7997725 | E | 87 | 100 |
| 7998175 | E | 24 | 100 |
| 7998177 | E | 31 | 100 |
+---------+-----+------+------+
15 rows in set, 5 warnings (25.38 sec)
说明:
1.把id格式化为10位长度字符串
2.用group_concat拼接id,并实现排序
3.用表达式11*3 - 1(其中3为要获取的记录数)截取排在前面的id
4.用find_in_set筛选出实际的记录数据