Shulou(Shulou.com)05/31 Report--

This article shows you how to sort according to the specified fields in MySQL. The content is concise and easy to understand, which will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

test data

Drop table a

Create table a (x varchar (10), y varchar (10))

Insert into a values ('yujx','all'), (' oracle','pc'), ('mysql','mobile')

# the test data in Table an are as follows

MySQL > select * from a

+-+ +

| | x | y |

+-+ +

| | yujx | all |

| | oracle | pc |

| | mysql | mobile |

+-+ +

3 rows in set (0.00 sec)

# default sort (ascending or descending) results by y

MySQL > select * from an order by y

+-+ +

| | x | y |

+-+ +

| | yujx | all |

| | mysql | mobile |

| | oracle | pc |

+-+ +

3 rows in set (0.00 sec)

MySQL > select * from an order by y desc

+-+ +

| | x | y |

+-+ +

| | oracle | pc |

| | mysql | mobile |

| | yujx | all |

+-+ +

3 rows in set (0.00 sec)

Now if you want to sort in the order of mobile- > all- > pc, you can use the following method

Method 1: use the FIND_IN_SET (str,strlist) function

MySQL > select * from an order by find_in_set (yJournal, mobile, all, PC')

+-+ +

| | x | y |

+-+ +

| | mysql | mobile |

| | yujx | all |

| | oracle | pc |

+-+ +

3 rows in set (0.00 sec)

Method 2: use FIELD (str,str1,str2,str3,...) Function

The main purpose of the # FIELD function is to return the position of the value in the following list, as follows

MySQL > select XJI YJI field (YMIT Mobilization, pcDLY, pcDNAL, pcDNAL) sort_Nu from an order by field

+-+

| | x | y | sort_Nu |

+-+

| | mysql | mobile | 1 | |

| | oracle | pc | 2 | |

| | yujx | all | 3 | |

+-+

3 rows in set (0.00 sec)

Method 3: use the SUBSTRING_INDEX (str,delim,count) function

MySQL > select * from an order by substring_index ('mobile,all,pc',y,1)

+-+ +

| | x | y |

+-+ +

| | mysql | mobile |

| | yujx | all |

| | oracle | pc |

+-+ +

3 rows in set (0.00 sec)

# looking at the value of substring_index ('mobile,all,pc',y,1) below, we can see that the order in which y is sorted by the value of column b is mobile,all,pc.

MySQL > select YJ substringency index ('mobile,all,pc',y,1) b from a

+-+ +

| | y | b | |

+-+ +

| | all | mobile, | |

| | pc | mobile,all, | |

| | mobile |

+-+ +

3 rows in set (0.00 sec)

Method 4: use case when

MySQL > select xjimeng case when yearly mobile 'then 1 when yawning all' then 2 when yawning pc' then 3 end sort_nu from an order by case when yawning mobile` then 1 when yearly mobile' then 2 when yawning PC 'then 3 end

+-+

| | x | y | sort_nu |

+-+

| | mysql | mobile | 1 | |

| | yujx | all | 2 | |

| | oracle | pc | 3 | |

+-+

3 rows in set (0.00 sec)

The above is how to sort according to the specified fields in MySQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.