How does mysql sort iteratively based on the value of a field?
Table T1 has some data, and there is a field C1 for data classification. Can you return the sort according to the cyclic classification of C1 in SELECT?
suppose there are three kinds of data in C1 field, which are "class 1", "class 2" and "class 3". The expected results are as follows:
if the data of "class 1" has been sorted out, then class 2 and class 3 are sorted for the last time
C1
.
Class 1, data.
Class 2, data.
Class 3, data.
Class 1, data.
Class 2, data.
Class 3, data.
Class 1, data.
it is not appropriate to use mysql to deal with this logic. It is recommended to deal with it on the code layer
. Why not add a column as a sorting sequence and do it manually?
does not quite understand your needs, mysql may be able to achieve, but it is not recommended to use mysql processing, do not know your direction is the program or DBA, if it is a program, it is recommended to deal with the program. Because the database is shared, if you operate in the database, it will consume the performance of the database, resulting in congestion is very bad.
author: Flying Bird
Link: https://www.zhihu.com/questio.
Source: Zhihu
mysql> select * from t limit 10;
< table >
< thead > < tr >
< th > id < / th >
< th > c < / th >
< / tr > < / thead >
< tbody >
< tr >
< td > 521 < / td >
< td > group2 < / td >
< / tr >
< tr >
< td > 522 < / td >
< td > group2 < / td >
< / tr >
< tr >
< td > 523 < / td >
< td > group3 < / td >
< / tr >
< tr >
< td > 524 < / td >
< td > group2 < / td >
< / tr >
< tr >
< td > 525 < / td >
< td > group3 < / td >
< / tr >
< tr >
< td > 526 < / td >
< td > group2 < / td >
< / tr >
< tr >
< td > 527 < / td >
< td > group2 < / td >
< / tr >
< tr >
< td > 528 < / td >
< td > group3 < / td >
< / tr >
< tr >
< td > 529 < / td >
< td > group2 < / td >
< / tr >
< tr >
< td > 530 < / td >
< td > group1 < / td >
< / tr >
< / tbody >
< / table >
10 rows in set (0.00 sec)
mysql> select c,count(*) from t group by c;
< table >
< thead > < tr >
< th > c < / th >
< th > count (*) < / th >
< / tr > < / thead >
< tbody >
< tr >
< td > group1 < / td >
< td > 13 < / td >
< / tr >
< tr >
< td > group2 < / td >
< td > 19 < / td >
< / tr >
< tr >
< td > group3 < / td >
< td > 28 < / td >
< / tr >
< / tbody >
< / table >
3 rows in set (0.00 sec)
mysql> select c,id from t ,(select @x:=0,@y:=0,@z:=0) x order by
-> case when c='group1' then @x:=@x+1
-> when c='group2' then @y:=@y+1
-> when c='group3' then @z:=@z+1 end
-> ,c limit 20;
< table >
< thead > < tr >
< th > c < / th >
< th > id < / th >
< / tr > < / thead >
< tbody >
< tr >
< td > group1 < / td >
< td > 530 < / td >
< / tr >
< tr >
< td > group2 < / td >
< td > 521 < / td >
< / tr >
< tr >
< td > group3 < / td >
< td > 523 < / td >
< / tr >
< tr >
< td > group1 < / td >
< td > 532 < / td >
< / tr >
< tr >
< td > group2 < / td >
< td > 522 < / td >
< / tr >
< tr >
< td > group3 < / td >
< td > 525 < / td >
< / tr >
< tr >
< td > group1 < / td >
< td > 535 < / td >
< / tr >
< tr >
< td > group2 < / td >
< td > 524 < / td >
< / tr >
< tr >
< td > group3 < / td >
< td > 528 < / td >
< / tr >
< tr >
< td > group1 < / td >
< td > 540 < / td >
< / tr >
< tr >
< td > group2 < / td >
< td > 526 < / td >
< / tr >
< tr >
< td > group3 < / td >
< td > 533 < / td >
< / tr >
< tr >
< td > group1 < / td >
< td > 541 < / td >
< / tr >
< tr >
< td > group2 < / td >
< td > 527 < / td >
< / tr >
< tr >
< td > group3 < / td >
< td > 538 < / td >
< / tr >
< tr >
< td > group1 < / td >
< td > 545 < / td >
< / tr >
< tr >
< td > group2 < / td >
< td > 529 < / td >
< / tr >
< tr >
< td > group3 < / td >
< td > 539 < / td >
< / tr >
< tr >
< td > group1 < / td >
< td > 547 < / td >
< / tr >
< tr >
< td > group2 < / td >
< td > 531 < / td >
< / tr >
< / tbody >
< / table >
20 rows in set (0.00 sec)
The
idea of
is roughly to list table T. If group2 appears for the first time, this line is marked as grp=1, for the second time, and marked as grp=n for the nth occurrence of grp=2,. Group1 and group3 do the same, and then sort by grp and c.
