Atitit. mysql oracle with as mode temporary table mode CTE statement, reduce the subquery structure mssql SQL server..., atitit. mysqlcte

Atitit. mysql oracle with as mode temporary table mode CTE statement, reduce the subquery structure mssql SQL server..., atitit. mysqlcte

Atitit. Use of the CTE statement in the temporary table mode of mysql oracle with as mode to reduce the structural mssql SQL server of subqueries ..

 

1. with... as (...) in mysql how to express 1

2. subquery and query nesting facilitate readability. 2

3. The solution in MYSQL is: temporary table. 2

4. Seo blackhead keyword replacement link statistical sample 2

4.1. // --------- query the number of com websites 2

4.2. // ---------------------- query the number of net websites 2

4.3. --------------- joint statistics 3

5. with as + materialize is a good way to force subqueries to be executed first. 3

 

1. How should I express with... as (...) in mysql?

SQL statements are the most direct and common data access and operation methods for relational databases. It should be said that, among the success factors of RDBMS, the promotion of SQL language standards has an indelible credit. Mainstream databases such as Oracle, SQL Server, and DB2 are individually expanded based on standard SQL to achieve efficient database applications.

With as is a statement structure frequently used in Oracle SQL. Especially in some data warehouse mining systems, the SQL statement structure and nested structure are very complicated. With as can help us simplify the statement structure, improve statement readability, and improve statement execution plan control.

 

The WITH syntax is added to Oracle 9i to name the subquery in the query, which is placed at the beginning of the SELECT statement.

Author: old wow's paw Attilax iron, EMAIL: 1466519819@qq.com

Reprinted please indicate Source: http://blog.csdn.net/attilax

 

 

Db2, mssql, and oracle Support the use of with... ..

 

2. subquery and query nesting facilitate readability improvement,

The vast majority of subqueries and nesting can be completed through a series of connections. The database system is good at connection operations.

 

3. The solution in MYSQL is: temporary table.

You can refer to the MYSQL temporary table creation and usage in the MYSQL official manual.

 

 

4. Seo blackhead keyword replacement link statistical sample

 

4.1. // --------- query the number of com websites

DROP TEMPORARY TABLE

If exists tmp_table;

 

Create temporary table tmp_table SELECT

SUBSTR (url, 1, LOCATE ('. com', url) + 3) AS domain

FROM

Link_big

WHERE

LOCATE ('. com', url)> 0;

 

4.2. // ---------------------- query the number of net websites

 

DROP TEMPORARY TABLE

If exists tmp_table2;

 

Create temporary table tmp_table2

SELECT

SUBSTR (url, 1, LOCATE ('. net', url) + 3) AS domain

FROM

Link_big

WHERE

LOCATE ('. net', url)> 0;

 

4.3. --------------- joint statistics

SELECT

Count (*),

Domain

FROM

Tmp_table

GROUP

Domain

UNION

SELECT

Count (*),

Domain

FROM

Tmp_table2

GROUP

Domain

 

5. with as + materialize is a good way to force subqueries to be executed first.

 

Based on the above discussion, we can get the conditions and benefits of the with as application. In general, with as can bring two benefits. The first is statement Definition. The original complex subquery content is split into several statement blocks, improving readability. Second, for an automatic temporary table, you can retrieve the subquery data and perform organizational connections in the memory.

Finally, we would like to discuss the subquery unnested feature. In most cases, we think that Oracle can generate better execution plans. However, in practice, we have also found many such scenarios: The subquery speed is very fast and the data volume is small, but the speed is slow after the connection. The result is that the subquery in the execution plan is opened.

By default, Oracle has the "low computing" feature when estimating the number of multi-Condition Statement result sets. That is, the estimated result set quantity must be smaller than the actual quantity because the column correlation is not taken into account by default. Therefore, most query expansions are passed.

In this case, with as + materialize is a good way to force subqueries to be executed first.

 

 

Reference

Create and delete a mysql-type SQL statement-mysql tutorial-data database-1.

View with asuse -realkid4-itpubbo .htm from five SQL statements