mysql 分组取top_MySQL获取分组后的TopN条数据

表结构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>

SQLSET 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的完整数据即可.