MySQL group then take the TOPN data sorted by some fields in each group

MySQL group then take the TOPN data sorted by some fields in each group

Build table

CREATE TABLE ' t ' (  ' a ' int (one) default null,  ' b ' int (one) default null,  ' C ' int (one) ' Default ' null ',  ' itime ' t Imestamp not NULL default current_timestamp on UPDATE current_timestamp) engine=innodb default Charset=utf8;

Inserting test data

INSERT INTO  t (' A ', ' B ', ' C ', ' itime ') VALUES (' 1 ', ' 1 ', ' 1 ', ' 2014-12-04 19:07:01 '); insert into  t (' A ', ' B ', ' C ', ' Itime ') VALUES (' 1 ', ' 1 ', ' 2 ', ' 2014-12-04 19:07:02 '); INSERT into T (' A ', ' B ', ' C ', ' itime ') VALUES (' 1 ', ' 1 ', ' 3 ', ' 2014- 12-04 19:07:03 '); insert into T (' A ', ' B ', ' C ', ' itime ') VALUES (' 1 ', ' 1 ', ' 4 ', ' 2014-12-04 19:07:04 '); insert into  t (' A ', ' B ', ' C ', ' itime ') VALUES (' 1 ', ' 2 ', ' 1 ', ' 2014-12-04 19:07:01 '); INSERT into T (' A ', ' B ', ' C ', ' itime ') VALUES (' 1 ', ' 2 ', ' 2 ', ' 2014-12-04 19:07:02 '); INSERT into  t (' A ', ' B ', ' C ', ' itime ') VALUES (' 1 ', ' 2 ', ' 3 ', ' 2014-12-04 19:07:03 '); INS ERT into T (' A ', ' B ', ' C ', ' itime ') VALUES (' 1 ', ' 2 ', ' 4 ', ' 2014-12-04 19:07:04 '); INSERT into  t (' A ', ' B ', ' C ', ' itime ') VALUES (' 1 ', ' 2 ', ' 5 ', ' 2014-12-04 19:07:05 '); INSERT into T (' A ', ' B ', ' C ', ' itime ') VALUES (' 1 ', ' 2 ', ' 6 ', ' 2014-12-04 19:07:06 ');

Group by A/b, and follow the Itime field in flashbacks, taking each group of TOP3

SELECTT.A,  T.b,substring_index (Group_concat (Ifnull (t.c,0) ORDER byt.itime DESC), ",", 3) C,substring_index (group _concat (T.itimeorder byt.itime DESC), ",", 3) timefromt Tgroup byt.a, t.b;

The result set is as follows:

Attention:

First, this method requires the upper application to do one more processing: The upper application takes out the result set, the data according to the comma, then cut into TOPN data, (note that there may be some groups do not have TOPN data)

Second, ifnull judgment is very important, otherwise MySQL will put the next piece of data in. If the C column is null, set a default value of 0 to prevent the fourth data (1) from being put in.

There is no ifnull the wrong answer:

PS: This feature cannot be implemented if it is not a last resort. It is not recommended to use such complex SQL statements in MySQL.

If you have a better way of writing, please reply, let me know, thank you

MySQL group then take the TOPN data sorted by some fields in each group