MySQL uses User-Defined variable simulation analysis functions

MySQL uses a user-defined variable simulation analysis function to initialize the experiment structure and data: createtabletest (tidvarchar (32) primarykey, statint

MySQL uses a user-defined variable simulation analysis function to initialize the experiment structure and data: create table test (tid varchar (32) primary key, stat int

MySQL uses a user-defined variable simulation analysis function to initialize the experiment structure and data:

Create table test (

Tid varchar (32) primary key,

Stat int not null,

Createtime timestamp not null

);

Insert into test (tid, stat, createtime) VALUES ('ac551ad7ba3f9067b19ac8bb20caca6d ',-1, '2017-08-20 10:01:09 ');

Insert into test (tid, stat, createtime) VALUES ('dc9a4438e577f4b08f7033a305544d47 ',-1, '2017-08-20 10:00:19 ');

Insert into test (tid, stat, createtime) VALUES ('23055228532bbba5a68d6ada11bcf33f ',-1, '2017-08-20 09:58:32 ');

Insert into test (tid, stat, createtime) VALUES ('5711ee1610d07a55e64c7948667de6e8',-1, '2017-08-20 09:58:09 ');

Insert into test (tid, stat, createtime) VALUES ('035e06d8afd681a9904bd74e9860f8cb ',-1, '2017-08-20 09:57:52 ');

Insert into test (tid, stat, createtime) VALUES ('3890efc08f37fa489a4e130cb04f71ac',-1, '2017-08-20 09:57:48 ');

Insert into test (tid, stat, createtime) VALUES ('1b6ed9db663dae470b45c722a61d08b0',-1, '2017-08-20 09:56:40 ');

Insert into test (tid, stat, createtime) VALUES ('8fb3409015e6b2cf85ba6ee90f15b58f',-1, '2017-08-20 09:54:40 ');

Insert into test (tid, stat, createtime) VALUES ('0badb1f4c2b1a89f1c473b992183add3 ',-1, '2017-08-20 09:54:33 ');

Insert into test (tid, stat, createtime) VALUES ('89b8af5eb473b2d4f50dd9e10773a9cc',-1, '2017-08-20 09:53:54 ');

Insert into test (tid, stat, createtime) VALUES ('77923a70000110224b5f94e7d0bd297de', 2, '2017-08-19 17:13:17 ');

Insert into test (tid, stat, createtime) VALUES ('0df1da77cfdbe64edcd4d645197174af', 2, '2017-08-19 12:20:21 ');

Insert into test (tid, stat, createtime) VALUES ('43daef6bfbc46dbfdbb97e71_3dab30', 2, '2017-08-19 09:54:08 ');

Insert into test (tid, stat, createtime) VALUES ('d512c510391314f48054c6c9ab9535c', 2, '2017-08-19 09:23:41 ');

Insert into test (tid, stat, createtime) VALUES ('f7c123143752498b7c9a226a9583ae49 ', 2, '2017-08-19 01:14:21 ');

Insert into test (tid, stat, createtime) VALUES ('da6a9a78897a42ae0a565cd0fabd76bb ', 2, '2017-08-18 21:59:46 ');

Insert into test (tid, stat, createtime) VALUES ('9cd3f83ab04120504a880523702491d7', 2, '2017-08-18 16:26:30 ');

Insert into test (tid, stat, createtime) VALUES ('4dfa129ba64e7062afa37e56bb9632de ', 2, '2017-08-18 14:32:41 ');

Insert into test (tid, stat, createtime) VALUES ('a9a731870e1c02278c22ce1ab36fa43c ', 2, '2017-08-18 14:31:26 ');

Insert into test (tid, stat, createtime) VALUES ('97f39d2a1e519f99e602e72cfc45fe0c ', 2, '2017-08-17 11:47:52 ');

Insert into test (tid, stat, createtime) VALUES ('31ba95265a96971221ddf9320c79eed8', 3, '2017-08-20 02:08:50 ');

Insert into test (tid, stat, createtime) VALUES ('060d92222edcb6f583cb4cd0244aadc0 ', 3, '2017-08-20 02:05:54 ');

Insert into test (tid, stat, createtime) VALUES ('7d3eb4ea201906b08e961b9fe7726fd4 ', 3, '2017-08-20 02:00:11 ');

Insert into test (tid, stat, createtime) VALUES ('c633bc16cb8c3bb4ffa7f00682701b92', 3, '2017-08-20 01:54:22 ');

Insert into test (tid, stat, createtime) VALUES ('e43bb7e7272139712b389e3feabc068f', 3, '2017-08-20 01:49:36 ');

Insert into test (tid, stat, createtime) VALUES ('bdabf3d80fb097222368cb30gj48117 ', 3, '2017-08-20 01:48:48 ');

Insert into test (tid, stat, createtime) VALUES ('170e2bdc11d517a56b7ce23d85633e42 ', 3, '2017-08-20 01:46:56 ');

Insert into test (tid, stat, createtime) VALUES ('7e79f6065ae8bb215cee43a4efbcd852 ', 3, '2017-08-20 01:44:17 ');

Insert into test (tid, stat, createtime) VALUES ('04728676e3305de05a18333ddfc76c01 ', 3, '2017-08-20 01:39:05 ');

Insert into test (tid, stat, createtime) VALUES ('d987176d350d4fefcc92b9a7ebb4f288 ', 3, '2017-08-20 01:35:52 ');

COMMIT;

Requirements:
Stat indicates the status, while Createtime indicates the creation time.
Queries the content of the last three records in each status.

This requirement is almost unknown.

But this is the MySQL database. He has no analysis function and can only simulate it with custom variables.
The stat Field of the test table is the group ID.
Gid is the group ID of the previous record,
Cgid is the group ID of the current record,
If the gid and cgid are different, the Group ID has changed and the Rank sorting is reset.
In this way, Rank is auto-incrementing according to each group, and the first N records are enough.

SELECT t3.tid, t3.stat, t3.createtime

FROM (SELECT @ gid: = @ cgid, @ cgid: = t1.stat, if (@ gid = @ cgid, @ rank: = @ rank + 1, @ rank: = 1) AS rank, t1 .*

FROM (SELECT *

FROM test

Order by stat, createtime DESC

) T1, (SELECT @ gid: = 1, @ cgid: = 1, @ rank: = 1) t2
) T3

WHERE t3.rank <= 3;
Result:

MySQL allows N records to be randomly extracted from each group.
(Features in the previous link)

SELECT t3.tid, t3.stat, t3.createtime

FROM (SELECT @ gid: = @ cgid, @ cgid: = t1.stat, if (@ gid = @ cgid, @ rank: = @ rank + 1, @ rank: = 1) AS rank, t1 .*

FROM (SELECT *

FROM test

Order by stat, rand ()

) T1, (SELECT @ gid: = 1, @ cgid: = 1, @ rank: = 1) t2
) T3