The implementation method of simultaneous use of union and order by in Mysql _mysql

The Union and order by in MySQL can be used together, but some minor problems need to be noticed in use, as illustrated by the examples below. First look at the T1 table below.

1, if directly with the following SQL statement will be an error: incorrect usage of the UNION and order by.

SELECT * from T1 WHERE username like ' l% ' ORDER by score ASC
UNION
SELECT * from T1 WHERE username like '%m% ' ORDER by score ASC

Because the Union can only use an order by when there are no parentheses, there are 2 ways to correct the error. As follows:

(1) The preceding order can be removed and changed to read as follows:

SELECT * from T1 WHERE username like ' l% '
UNION
SELECT * from T1 WHERE username like '%m% ' ORDER by score ASC

The meaning of this SQL is to first union and then to order the entire result set by.

(2) can be divided into the following two queries by means of parentheses, as follows:

(SELECT * from T1 WHERE username like ' l% ' ORDER by Sroce ASC)
UNION
(SELECT * from T1 WHERE username like '%m% ' ORDER by score ASC)

The purpose of this approach is to have two result sets separately, and then to union the two result sets. But you'll find that this is not an error, but two order by has no effect, so it should be changed as follows:

SELECT * FROM
(SELECT * from T1 WHERE username like ' l% ' ORDER by score ASC) T3
UNION
SELECT * FROM
(SELECT * from T1 WHERE username like '%m% ' ORDER by score ASC) T4

In other words, the order by cannot appear directly in the Union's clause, but it can appear in the clause of the clause.

2. By the way, the difference between Union and union all.

Union filters out duplicate rows in two result sets, and union all does not filter out duplicate rows.

The above MySQL in the Union and order by the simultaneous use of the implementation method is small series to share all the content, hope to give you a reference, but also hope that we support cloud habitat community.