UNION usage and sorting in Mysql

This problem was recently encountered in writing projects. We need to combine the two SELECT query results for grouping and sorting, and thought of using the union method, the union operation with TP cannot complete complex union operations at all. So I collected a collection and explained the usage of union and how to implement complex union operations in TP. I. UNION usage UNI

This problem was recently encountered in writing projects. We need to combine the two SELECT query results for grouping and sorting, and thought of using the union method, the union operation with TP cannot complete complex union operations at all. So I collected a collection and explained the usage of union and how to implement complex union operations in TP. I. UNION usage UNI

This problem was recently encountered in writing projects. We need to combine the two SELECT query results for grouping and sorting, and thought of using the union method, the union operation with TP cannot complete complex union operations at all. So I collected a collection and explained the usage of union and how to implement complex union operations in TP.

I. UNION usage

1. UNION syntax

SELECT...

UNION [ALL | DISTINCT]

SELECT...

[UNION [ALL | DISTINCT]

SELECT...]

UNION is used to combine the results from many SELECT statements into a result set.

Columns selected at the corresponding position of each SELECT statement should have the same type. (For example, the first column selected by the first statement should be of the same type as the first column selected by other statements .) The column name used in the first SELECT statement is also the name of the Column Used for the result.

The SELECT statement is a regular selection statement, but is subject to the following restrictions:

· Only the last SELECT statement can use into outfile.

· HIGH _ PRIORITY cannot be used with SELECT statements that are part of the UNION operation. If you specify HIGH_PRIORITY for the first SELECT statement, it does not work. If you specify HIGH_PRIORITY for other subsequent SELECT statements, a syntax error occurs.

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 ALL is specified, ALL matched rows are obtained from ALL used SELECT statements.

The DISTINCT keyword is a self-selected word and does not play any role, but can be used in the syntax according to the requirements of the SQL standard. (In MySQL, DISTINCT represents the default working nature of a shared body .)

You can mix union all and union distinct in the same query. The mixed UNION type is treated in this way, that is, the DISTINCT shared body overwrites ALL the shared bodies located on the left. The DISTINCT shared body can be explicitly generated using union distinct, or implicitly generated using UNION (without the DISTINCT or ALL keyword.

If you want to use the order by or LIMIT clause to classify or LIMIT all UNION results, parentheses should be added to a single SELECT statement, and put order by or LIMIT behind the last one. The following example uses both clauses:

The Code is as follows:

(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 statement cannot be referenced BY columns that contain table names (that is, names in tbl_name.col_name format. You can provide a column alias in the first SELECT statement, and refer to the alias in order by, or use the column position to refer to the column in order. (Aliases are preferred because column locations are not recommended .)

In addition, if a column with a category has an alias, the order by clause must reference the alias instead of the column name. The first statement in the following statement must be run, but the second statement will fail. An error occurs when the 'A' column is unknown in 'order clause:

The Code is as follows:

(SELECT a AS B FROM t) UNION (SELECT...) order by B;

(SELECT a AS B FROM t) UNION (SELECT...) ORDER BY;

To apply order by or LIMIT to an inpidual SELECT, place the clause inside the parentheses that enclose the SELECT.

To use order by or LIMIT for a single SELECT statement, the clause should be placed in parentheses. Parentheses contain SELECT.

The Code is as follows:

(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 );

2. instance Expansion

Union can combine two queries of the same table. the benefits of doing so are also very obvious. For example, in the blog application, you can use an SQL statement to display the top blog and ordinary blog pages.

The Code is as follows:

(
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: union requires that the two tables to be joined have the same number of data columns. If one table does not have the fields of the other table, NULL can be used instead.

Iii. Instances

UNION

The Code is as follows:

Select * from
(
Select * from...
Union all
Select * from...
) Aaa
Order by aaa. xxx desc;

UNION in

UNION filters out duplicate records after table link. Therefore, after table link, it sorts the generated result sets and deletes duplicate records before returning results. In most applications, duplicate records are not generated. The most common is the UNION of Process Tables and historical tables. For example:

The Code is as follows:

Select * from gc_dfys union select * from ls_jg_dfys

This SQL statement extracts the results of two tables at run time, sorts and deletes duplicate records using the sorting space, and finally returns the result set. If the table has a large amount of data, it may cause disk sorting.

Union all in MySQL

Union all simply merges the two results and returns them. In this way, if duplicate data exists in the two returned result sets, the returned result sets will contain duplicate data.

In terms of efficiency, union all is much faster than UNION. Therefore, if you can confirm that the two results of the merge do not contain duplicate data, use union all, as shown below:

The Code is as follows:

Select * from gc_dfys union all select * from ls_jg_dfys

Solve the union Sorting Problem in mysql.

About union bidirectional sorting (mysql)

A friend in the group asked a mysql query question.

This is the requirement.

When the Forum displays the post list, the first three items are the most clicks, that is, query by number of clicks,

Remaining query by Time

Actually, both union sorting takes effect.

SQL code

The Code is as follows: