MySQL group sorting, mysql Group

MySQL group sorting, mysql Group

I. Problems

Product table
Platform id Product Name

Id ptid name
1 100 product 1
2 100 product 2
3 100 product 3
4 100 product 4
100 product 5
100 product 6
101 product 7
8 101 product 8
101 product 9
10 101 products 10
11 101 product 11

How to retrieve only three pieces of data from a platform based on the platform id (the order can be random)

Id ptid name
1 100 product 1
2 100 product 2
3 100 product 3
101 product 7
8 101 product 8
101 product 9

Ii. Create a table

Create table p_a (id int, ptid varchar (20), name varchar (20) insert into p_a (id, ptid, name) values (1, '20140901 ', 'product 1'); insert into p_a (id, ptid, name) values (2, '000000', 'product 2'); insert into p_a (id, ptid, name) values (3, '20140901', 'product 3'); insert into p_a (id, ptid, name) values (4, '20160901', 'product 4 '); insert into p_a (id, ptid, name) values (5, '000000', 'product 5'); insert into p_a (id, ptid, name) values (6, '123', 'product 6'); insert into p_a (id, ptid, name) values (7, '123', 'product 7'); insert into p_a (id, ptid, name) values (8, '000000', 'product 8'); insert into p_a (id, ptid, name) values (9, '20140901 ', 'product 9'); insert into p_a (id, ptid, name) values (10, '000000', 'product 10'); insert into p_a (id, ptid, name) values (11, '000000', 'product 11 ');

Iii. SQL

select * from (select a.*,case when @oldptid=ptid then @lagfield:=@lagfield+1 else @lagfield:=1 end rn,@oldptid:=ptidfrom p_a a,(select @lagfield:=0,@oldptid:='') r) a where rn<=3

Iv. Summary

MySQL groups data by ptid and marks the sequence numbers in each group, and then extracts records from each group based on the sequence numbers.

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.