Union usage and ordering in MySQL

Recently also in the writing project encountered in this problem, need to combine two select query results grouped sorted, think of the Union method, with TP union operation can not complete the complex union operation, so the search for a moment, first say the use of Union, How to implement the complex union operation in TP.

One, UNION usage

A Union grammar

SELECT ...
Union[all | DISTINCT]
SELECT ...
[UNION [All | DISTINCT]
SELECT ...]

The union is used to combine the results from many SELECT statements into a single result set.

The selected columns that are listed at the corresponding location of each SELECT statement should have the same type. (for example, the first column selected by the first statement should have the same type as the first column selected by other statements.) www.111cn.net) The column name used in the first SELECT statement is also used for the result's column name.

The SELECT statement is a regular selection statement, but is qualified as follows:

• Only the last SELECT statement can use into outfile.
· High_priority cannot be used concurrently with a SELECT statement that is part of a union. If you specify high_priority for the first select, it will not work. If you specify high_priority for other subsequent SELECT statements, a syntax error is generated.
 
If you do not use the keyword all for union, all returned rows are unique, as if you have used distinct for the entire result set. If you specify all, you get all the matching rows from all the used SELECT statements.
 
DISTINCT keyword is an optional word that has no effect, but is allowed in syntax according to the requirements of the SQL standard. (in MySQL, distinct represents the default working nature of a common body.)
 
You can mix union all and union DISTINCT in the same query. The mixed union type is treated in this manner, that is, the distinct common body overwrites all the all shared bodies located on its left. The distinct common body can be explicitly generated using union distinct or implicitly generated using union (followed by no distinct or all keywords).
&NBSP
If you want to categorize or limit all union results by using an ORDER BY or limit clause, you should add parentheses to a single SELECT statement and place the order by or limit behind the last one. The following examples use these two clauses at the same time:

The code is as follows	Copy Code
(SELECT A from tbl_name WHERE a=10 and B=1) UNION (SELECT A from tbl_name WHERE a=11 and b=2) Order by a LIMIT 10; (SELECT A from tbl_name WHERE a=10 and B=1) UNION (SELECT A from tbl_name WHERE a=11 and b=2) Order by a LIMIT 10;

This order by cannot use a column reference that includes a table name (that is, a name in tbl_name.col_name format). You can provide a column alias in the first SELECT statement, see the alias in order by, or use the column position to refer to the column in an order by. (aliases are preferred, because column locations are not recommended.) ）

In addition, if a column with a taxonomy has an alias, the ORDER by WWW.111CN.NET clause must reference the alias, not the column name. The first statement in the following statement must be run, but the second will fail, appearing in ' order clause ' with an unknown column ' a ' ERROR:

The code is as follows	Copy Code
(SELECT A as B from T) UNION (SELECT ...) Order by B; (SELECT A as B from T) UNION (SELECT ...) Order by A; To apply order BY or LIMIT to a individual select, place the clause inside the parentheses that enclose the select.

To use order by or limit for a single select, insert the sentence in parentheses. The parentheses contain a select.

The code is as follows	Copy Code
(SELECT A from tbl_name WHERE a=10 and B=1 order by a LIMIT 10) UNION (SELECT A from tbl_name WHERE a=11 and b=2 order by a LIMIT 10);

Two instance Extensions

Union can unite two queries for the same table. The benefits are also very obvious, such as in the blog application, you can use a SQL statement to achieve the top blog and the regular blog page display.

The code is as follows	Copy Code
( SELECT * from ' blog ' WHERE Top=1 Order by created DESC ) UNION ( SELECT * From ' blog ' WHERE top = 0 Order by created DESC ) LIMIT 2, 3

Note: The Union requires that two of the combined tables are looking for as many data columns as there are fields in one table that do not have another table, you can use NULL instead

Third, the example

UNION

The code is as follows	Copy Code
SELECT * FROM ( SELECT * From ... UNION ALL SELECT * From ... ) AAA ORDER BY aaa.xxx Desc;

The Union in the
The Union filters out duplicate records after a table link, so the resulting result sets are sorted after the table is connected, the duplicate records are deleted, and the results are returned. Most applications do not produce duplicate records, the most common of which is the process table and the History table Union. Such as:

The code is as follows	Copy Code
SELECT * FROM Gc_dfys Union SELECT * Ls_jg_dfys

This SQL is run with the result of two tables first, then sorted by sorting to delete duplicate records, and finally return the result set, if the table data volume may cause the disk to sort.

The union ALL in MySQL

The Union all simply merges the two results and returns. Thus, if the returned two result sets have duplicate data, the returned result set will contain duplicate data.
In terms of efficiency, union All is much faster than union, so if you can confirm that the merged two result sets do not contain duplicate data, then use Union ALL, as follows:

The code is as follows	Copy Code
SELECT * FROM Gc_dfys UNION ALL SELECT * FROM Ls_jg_dfys

Solve the problem of union ordering in MySQL.

About union bidirectional ordering (MySQL)
The group of Friends asked a MySQL query question
This is the demand.
The forum wants to display the list of posts when the top three is the most clicks, that is, click number of inquiries,
The rest of the time query
The fact is that the sort of the two Union takes effect

SQL code

The code is as follows	Copy Code
(SELECT * FROM (colnum desc) a); (SELECT * FROM table order by Colnum limit 0,2 ) UNION (SELECT * FROM (SELECT * to table ORDER BY colnum Desc) a);

It is estimated that many people will have this demand.

The code is as follows	Copy Code
Select *,1 as Ord from ' women ' where status = 2 and title like '%2014% ' or title like '%/fall/winter% ' Union (select *,2 as Ord from ' Women ' where status = 2 and keywords like '%2014% ' or keywords like '% fall/Fall ') Order BY ord,id desc limit 0, 15;