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

This article is about how to sort in MySQ. Xiaobian thinks it is quite practical, so share it with everyone for reference. Let's follow Xiaobian and have a look.

ORDER BY field name ascending/descending order, I believe that friends who come in know this sort statement, but encounter some special sort, just use the field name can not meet the needs, here are a few sorting methods I have encountered:

I. Preparations

In order to better demonstrate and understand, prepare a student form first, add three fields of number, name and grade, and insert several pieces of data, as shown in the figure:

II. Conditional ordering

Requirement 1: Ranking scores from high to low

The aunt selling vegetables on the street can knock, and it is easily completed by using ORDER BY examScore DESC (left picture below).

Requirement 2: The scores are sorted from high to low, and the ones that have not entered the scores are ranked first.

Customer experience is the most important, in order to facilitate the second entry of results, it is normal to put forward such a demand. To achieve this sort, the above statement cannot be implemented, so you need to use conditional sort, first determine the score is null and assign a maximum value, and then sort, such as ORDER BY IF(examScore IS NULL,101,examScore) DESC can also be easily implemented (see the right figure below).

REQUIREMENTS REQUIREMENTS ONE REQUIREMENTS TWO STATEMENTS ORDER BY examScore DESCORDER BY IF(examScore IS NULL,101,examScore) DESC

effect

Third, custom sorting

Customers such as God, requirements are also common, for example, Zhang San Li four row at the top, other students according to the highest score from low to high. In this case, you need to use custom sorting. MySQL's FIELD function (which returns the index of the corresponding string) can help you achieve this.

Statement 1:

ORDER BY FIELD(studentName,'Zhang San',' Li Si') ASC, examScore DESC;

The result of running the above sentence is to arrange Zhang San Li Si to the end (left picture below). Naturally, to arrange Zhang San Li Si to the front, we will sort them in descending order.

Statement 2:

ORDER BY FIELD(studentName,'Li Si',' Zhang San') DESC, examScore DESC;

The result was exactly what he wanted (see right below).

Sentence 1 Sentence 2 Effect

It was later discovered that the FIND_IN_SET function could also be implemented, and using 1 million data tests, FIND_IN_SET performed better.

ORDER BY FIND_IN_SET(studentName,'Li Si, Zhang San') DESC, examScore DESC;

IV. Sorting of Chinese Pinyin Initials

Some friends are strange, Chinese character sorting directly using the ordinary ORDER BY field ASC easy to finish it, why say little known it.

In fact, when users create table fields using GBK character set, directly using ORDER BY field ASC can solve the problem. Some users use utf8 character set in order to prevent garbled characters, and simple sorting statements are helpless (left figure below). Therefore, it is OK to convert the fields to GBK when sorting (right figure below).

Statement ORDER BY studentName ASCORDER BY CONVERT(studentName USING GBK) ASC Effects

5. guess what you like

MySQL Remote Connection Error 1130 Solution

MySQL configuration master-slave synchronous backup

Use filters to prevent SQL injection

Thank you for reading! About "What are the sorting methods in MySQ" this article is shared here, I hope the above content can be of some help to everyone, so that everyone can learn more knowledge, if you think the article is good, you can share it to let more people see it!

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.