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从根本上是对典型子查询的直接替代。