MySQL分组获取前条记录

来源：互联网发布：淘宝上卖的牙齿矫正器编辑：程序博客网时间：2024/05/08 00:54

创建表：

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`()beginset @i = 8000000;while(@i > 0)doset @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筛选出实际的记录数据

0 0