sql - SQL WITH子句示例[duplicate](SQL WITH clause example [duplicate])

Question

Possible Duplicate:

(可能重复：)
Difference between CTE and SubQuery?

(CTE和SubQuery之间的区别？)

I was trying to understand how to use the WITH clause and the purpose of the WITH clause.

(我试图了解如何使用WITH子句和目的WITH子句。)

All I understood was, the WITH clause was a replacement for normal sub-queries.

(我所理解的是， WITH子句是普通子查询的替代品。)

Can anyone explain this to me with a small example in detail ?

(任何人都可以通过一个小例子详细解释这个问题吗？)

  ask by translate from so

Answer 1

The SQL WITH clause was introduced by Oracle in the Oracle 9i release 2 database.

(SQL WITH子句是Oracle在Oracle 9i第2版数据库中引入的。)

The SQL WITH clause allows you to give a sub-query block a name (a process also called sub-query refactoring), which can be referenced in several places within the main SQL query.

(SQL WITH子句允许您为子查询块提供一个名称（一个也称为子查询重构的进程），可以在主SQL查询中的几个位置引用该名称。)

The name assigned to the sub-query is treated as though it was an inline view or table.

(分配给子查询的名称被视为内联视图或表。)

The SQL WITH clause is basically a drop-in replacement to the normal sub-query.

(SQL WITH子句基本上是普通子查询的替代品。)

Syntax For The SQL WITH Clause

(SQL WITH子句的语法)

The following is the syntax of the SQL WITH clause when using a single sub-query alias.

(以下是使用单个子查询别名时SQL WITH子句的语法。)

WITH <alias_name> AS (sql_subquery_statement)
SELECT column_list FROM <alias_name>[,table_name]
[WHERE <join_condition>]

When using multiple sub-query aliases, the syntax is as follows.

(使用多个子查询别名时，语法如下。)

WITH <alias_name_A> AS (sql_subquery_statement),
<alias_name_B> AS(sql_subquery_statement_from_alias_name_A
or sql_subquery_statement )
SELECT <column_list>
FROM <alias_name_A>, <alias_name_B> [,table_names]
[WHERE <join_condition>]

In the syntax documentation above, the occurrences of alias_name is a meaningful name you would give to the sub-query after the AS clause.

(在上面的语法文档中， alias_name的出现是您在AS子句之后为子查询提供的有意义的名称。)

Each sub-query should be separated with a comma Example for WITH statement .

(每个子查询都应该用逗号示例for WITH语句分隔。)

The rest of the queries follow the standard formats for simple and complex SQL SELECT queries.

(其余查询遵循简单和复杂SQL SELECT查询的标准格式。)

For more information: http://www.brighthub.com/internet/web-development/articles/91893.aspx

(有关更多信息，请访问： http ： //www.brighthub.com/internet/web-development/articles/91893.aspx)