SQL WITH子句

SQL | 带子句 (SQL | WITH Clause)

The SQL WITH statement was presented by Oracle in the Oracle 9i discharge 2 database. The SQL WITH provision permits you to give a sub-question a name (a procedure additionally called sub-inquiry refactoring), which can be referenced in a few places inside the principle SQL inquiry.

Oracle在Oracle 9i排放2数据库中提出了SQL WITH语句 。 SQL WITH设置允许您为子问题提供一个名称(该过程另外称为子查询重构 )，可以在基本SQL查询内部的几个地方引用该名称。

• The statement is utilized for characterizing a transitory connection to such an extent that the yield of this impermanent connection is accessible and is utilized by the question that is related to the WITH provision.

  该语句用于描述临时连接的程度，以至于可以访问此无常连接的结果，并被与WITH条款相关的问题所利用。

• Questions that have a related WITH provision can likewise be composed utilizing settled sub-inquiries yet doing so adds greater unpredictability to peruse/investigate the SQL inquiry.

  与WITH条款相关的问题同样可以利用已解决的子查询来构成，但这样做会增加阅读/调查SQL查询的不可预测性。

• WITH condition isn't upheld by all database framework.

  不是所有数据库框架都支持WITH条件。

• The name doled out to the sub-inquiry is treated just as it was an inline view or table

  分发给子查询的名称将被视为内联视图或表格

• The SQL WITH condition was presented by Oracle in the Oracle 9i discharge 2 database.

  Oracle在Oracle 9i排放2数据库中提出了SQL WITH条件。

Syntax:

句法：

    WITH <tablename>(attribute) AS
    ( 	
	    SELECT <columnname(attribute)>
	    FROM <tablename>
	    SELECT <attribute>
	    FROM <tablename>
	    WHERE <condition>;
    )

NOTE: At the point when a question with a WITH condition is executed, first the inquiry referenced inside the proviso is assessed and the yield of this assessment is put away in an impermanent connection. Following this, the principle question related to the WITH provision is at last executed that would utilize the brief connection delivered.

注意 ：在执行具有WITH条件的问题时，首先会评估附带条件中引用的查询，然后将这种评估的结果放在一个永久连接中。 此后，与WITH条款有关的主要问题将最终执行，该问题将利用交付的简短连接。

Let's understand this concept with the help of examples:

让我们借助示例了解这个概念：

Example1: Find all the students who have marks more than the marks of total students.

示例1：查找所有分数超过总分数的学生。

Solution: Name of table: Student

解决方案：表格名称：学生

RollNo	Name	Marks
201	Aniket Rana	65
202	Aarushi Mehta	60
203	Pankaj Sinha	72
204	Hardik Sharma	79
205	Avi Singh	82

卷号	名称	分数
201	阿妮克特·拉娜(Aniket Rana)	65
202	荒西梅塔	60
203	潘卡·辛哈(Pankaj Sinha)	72
204	哈迪克·夏尔马	79
205	阿维·辛格	82

Query:

查询：

WITH temporaryTable(averagevalue) AS
(SELECT avg(marks)FROM Student),
SELECT rollno, name, marks 
FROM Student, temporaryTable 
WHERE Student.marks> temporaryTable.averagevalue;

Output:

输出：

RollNo	Name	Marks
203	Pankaj Sinha	72
204	Hardik Sharma	79
205	Avi Singh	82

卷号	名称	分数
203	潘卡·辛哈(Pankaj Sinha)	72
204	哈迪克·夏尔马	79
205	阿维·辛格	82

The average of marks of students comes out to be 71.6. Therefore, marks greater than 71.6 are displayed in the output.

学生的平均分数为71.6。 因此，输出中将显示大于71.6的标记。

Example 2: Find all restaurants where the total salary of all chefs in that restaurant is more than the average salary of all chefs in the database.

示例2：查找所有餐馆，其中该餐馆中所有厨师的总薪水高于数据库中所有厨师的平均薪水。

Solution: Name of Table: Chefs

解决方案：餐桌名称：厨师

ChefCode	Hotel	ChefName	Salary
123	The Lalit	Aditya	83250
234	Shangri La's	Vikas	125000
345	The Lalit	Sanjeev	264000
567	The Lalit	Ranveer	120000
545	Shangri La's	Kunal	250000

厨师代码	旅馆	厨师名称	薪水
123	拉利特	阿迪亚	83250
234	香格里拉	维卡斯	125000
345	拉利特	桑耶夫	264000
567	拉利特	兰维尔	120000
545	香格里拉	库纳尔	250000

Query:

查询：

WITH totalSalary(Hotel, total) AS
(
SELECT Hotel, sum(salary)
FROM Chefs
GROUP BY Hotel
),
hotelaverage(avgsalary) AS 
(
SELECT avg(salary)
FROM Chefs
)
SELECT Hotel
FROM totalsalary, hotelaverage
WHERE totalsalary.total > hotelaverage.avgsalary;

Output:

输出：

Hotel
Shangri La's
The Lalit

旅馆
香格里拉
拉利特

Points to Remember:

要记住的要点：

• The SQL WITH proviso is acceptable when utilized with complex SQL articulations instead of basic ones.

  与复杂SQL语句(而不是基本SQL语句)一起使用时，可以使用SQL WITH proviso。

• It likewise permits you to separate complex SQL inquiries into littler ones which make it simple for troubleshooting and preparing the mind-boggling questions.

  同样，它使您可以将复杂SQL查询分成较小的查询，这使故障排除和准备令人难以置信的问题变得简单。

• The SQL WITH proviso is fundamentally a drop-in substitution to the typical sub-inquiry.

  SQL WITH proviso从根本上是对典型子查询的直接替代。

翻译自: https://www.includehelp.com/sql/with-clause.aspx