MySQL获取分组后的TopN条数据
Contents
表结构
mysql> desc dce_his_cs_m_jyhqsj;
+---------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+---------------+------+-----+---------+----------------+
| QSJDATE | varchar(20) | YES | | NULL | |
| QSJDDDD | varchar(8) | NO | PRI | NULL | |
| QSJMMMM | varchar(4) | NO | PRI | NULL | |
| QSJHYBH | varchar(10) | NO | PRI | NULL | |
| QSJKHBH | varchar(10) | NO | PRI | NULL | |
| QSJKPJG | decimal(10,2) | YES | | NULL | |
| QSJKPTM | decimal(20,0) | YES | | NULL | |
| QSJZGJG | decimal(10,2) | YES | | NULL | |
| QSJZDJG | decimal(10,2) | YES | | NULL | |
| QSJSPJG | decimal(10,2) | YES | | NULL | |
| QSJSPTM | varchar(20) | YES | | NULL | |
| QSJCJSL | decimal(10,0) | YES | | NULL | |
| QSJSKCL | decimal(10,0) | YES | | NULL | |
| QSJSPCL | decimal(10,0) | YES | | NULL | |
| QSJBKCL | decimal(10,0) | YES | | NULL | |
| QSJBPCL | decimal(10,0) | YES | | NULL | |
| QSJSCCL | decimal(10,0) | YES | | NULL | |
| QSJBCCL | decimal(10,0) | YES | | NULL | |
| QSJZCCL | decimal(10,0) | YES | | NULL | |
| QSJSWTL | decimal(10,0) | YES | | NULL | |
| QSJBWTL | decimal(10,0) | YES | | NULL | |
| QSJZWTL | decimal(10,0) | YES | | NULL | |
| QSJAMPL | decimal(10,2) | YES | | NULL | |
| QSJZDFD | decimal(10,2) | YES | | NULL | |
| id | int(11) | NO | UNI | NULL | auto_increment |
+---------+---------------+------+-----+---------+----------------+
25 rows in set (0.00 sec)
mysql>
SQL
SET SESSION group_concat_max_len = 1000000;
mysql> select id, QSJDDDD, QSJMMMM, QSJCJSL from dce_his_cs_m_jyhqsj where FIND_IN_SET(id, (SELECT group_concat(id) as id from (select substring_index(group_concat(id order by id desc SEPARATOR ','),",",5) as id from dce_his_cs_m_jyhqsj GROUP BY QSJDDDD, qsjmmmm) as b)) order by QSJDDDD, QSJMMMM, QSJCJSL;
mysql> select id, QSJDDDD, QSJMMMM, QSJCJSL from dce_his_cs_m_jyhqsj where FIND_IN_SET(id, (SELECT group_concat(id) as id from (select substring_index(group_concat(id order by id desc SEPARATOR ','),",",5) as id from dce_his_cs_m_jyhqsj GROUP BY QSJDDDD, qsjmmmm) as b)) order by QSJDDDD, QSJMMMM, QSJCJSL;
+-------+----------+---------+---------+
| id | QSJDDDD | QSJMMMM | QSJCJSL |
+-------+----------+---------+---------+
| 2577 | 20160909 | 1401 | 14 |
| 10862 | 20160909 | 1401 | 28 |
| 10702 | 20160909 | 1402 | 21 |
| 11142 | 20160909 | 1402 | 35 |
| 11802 | 20160909 | 1402 | 57 |
| 10070 | 20160909 | 1402 | 98 |
| 9238 | 20160909 | 1402 | 120 |
| 11394 | 20160909 | 1403 | 5 |
| 10863 | 20160909 | 1403 | 11 |
| 11803 | 20160909 | 1403 | 14 |
| 11618 | 20160909 | 1403 | 56 |
| 12651 | 20160909 | 1403 | 95 |
| 11395 | 20160909 | 1404 | 53 |
| 12210 | 20160909 | 1404 | 64 |
| 12010 | 20160909 | 1404 | 67 |
| 12652 | 20160909 | 1404 | 89 |
| 12422 | 20160909 | 1404 | 114 |
| 9672 | 20160909 | 1405 | 9 |
| 12653 | 20160909 | 1405 | 22 |
| 11619 | 20160909 | 1405 | 26 |
| 10071 | 20160909 | 1405 | 64 |
| 12423 | 20160909 | 1405 | 118 |
思路:
使用group_concat
,然后将group_concat
的数据取出前N位(TopN),然后再通过FIND_IN_SET
来获取每个TopN的完整数据即可.