MySQL union and order by implementation method at the same time, mysqlunion

MySQL union and order by implementation method at the same time, mysqlunion

In MySQL, union and order by can be used together, but some minor problems need to be paid attention to during use. The following is an example. First, let's look at table t1 below.

1. If the following SQL statement is used directly, an error is returned: Incorrect usage of UNION and ORDER.

SELECT * FROM t1 WHERE username LIKE 'l % 'order by score ASC
UNION
SELECT * FROM t1 WHERE username LIKE '% m %' order by score ASC

Because union can only use one order by statement without parentheses, an error is returned. This statement has two ways to modify it. As follows:

(1) You can remove the previous order by and change it to the following:

SELECT * FROM t1 WHERE username LIKE 'l %'
UNION
SELECT * FROM t1 WHERE username LIKE '% m %' order by score ASC

This SQL statement means union first, and then order by for the entire result set.

(2) You can add parentheses for two queries 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 method is to first order by and then union the two result sets. However, you will find that although this method does not report an error, the two order by statements have no effect,So it should be changed to the following:

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

That is to say, order by cannot appear directly in the union clause, but 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, while union all does not.

In the above MySQL article, the implementation of union and order by is all the content shared by xiaobian. I hope to give you a reference and support for the customer's house.