Речення SQL WITH було представлено Oracle у базі даних Oracle 9i випуску 2. Речення SQL WITH дозволяє надати назву блоку підзапиту (цей процес також називають рефакторингом підзапиту), на який можна посилатися в кількох місцях основного запиту SQL.
- Речення використовується для визначення тимчасового відношення таким чином, що вихід цього тимчасового відношення є доступним і використовується для запиту, пов’язаного з реченням WITH.
- Запити, які мають пов’язане речення WITH, також можна писати за допомогою вкладених підзапитів, але це ускладнює читання/налагодження SQL-запиту.
- Речення WITH не підтримується всіма системами баз даних.
- Ім’я, призначене підзапиту, розглядається як вбудоване подання або таблиця
- Речення SQL WITH було представлено Oracle у базі даних Oracle 9i випуску 2.
Синтаксис:
WITH temporaryTable (averageValue) as (SELECT avg(Attr1) FROM Table) SELECT Attr1 FROM Table, temporaryTable WHERE Table.Attr1>temporaryTable.averageValue;>
висота зсуву
У цьому запиті речення WITH використовується для визначення тимчасового відношення temporaryTable, яке має лише 1 атрибут averageValue. averageValue містить середнє значення стовпця Attr1, описане в таблиці відношень. Інструкція SELECT, що слідує за пропозицією WITH, створить лише ті кортежі, де значення Attr1 у таблиці відношення перевищує середнє значення, отримане з пропозиції WITH.
Примітка: Коли виконується запит із пропозицією WITH, спочатку обчислюється запит, згаданий у пропозиції, а результат цієї оцінки зберігається у тимчасовому відношенні. Після цього нарешті виконується основний запит, пов’язаний із пропозицією WITH, який використовує створене тимчасове відношення.
Запити
Приклад 1: Знайдіть усіх працівників, зарплата яких перевищує середню зарплату всіх працівників.
Назва відношення: Співробітник
роздільник java
| EmployeeID | Ім'я | Зарплата |
|---|---|---|
| 100011 | Сміт | 50000 |
| 100022 | Білл | 94000 |
| 100027 | себе | 70550 |
| 100845 | Волден | 80000 |
| 115585 | Ерік | 60000 |
| 1100070 | Кейт | 69000 |
SQL запит:
WITH temporaryTable(averageValue) as (SELECT avg(Salary) from Employee) SELECT EmployeeID,Name, Salary FROM Employee, temporaryTable WHERE Employee.Salary>temporaryTable.averageValue;>
Вихід:
| EmployeeID | Ім'я | Зарплата |
|---|---|---|
| 100022 | Білл | 94000 |
| 100845 | Волден | 80000 |
Пояснення: Середня заробітна плата всіх працівників становить 70591. Отже, усі працівники, чия заробітна плата перевищує отриману середню, лежать у відношенні випуску.
приклад 2: Знайдіть усі авіакомпанії, у яких загальна зарплата всіх пілотів у цій авіакомпанії перевищує середню загальну зарплату всіх пілотів у базі даних.
рядок у дату
Назва відношення: Пілот
| EmployeeID | Авіакомпанія | Ім'я | Зарплата |
|---|---|---|---|
| 70007 | Airbus 380 | Кім | 60000 |
| 70002 | Боїнг | Лаура | 20000 |
| 10027 | Airbus 380 | Воля | 80050 |
| 10778 | Airbus 380 | Уоррен | 80780 |
| 115585 | Боїнг | Сміт | 25000 |
| 114070 | Airbus 380 | Кеті | 78000 |
SQL запит:
набір машинописів
WITH totalSalary(Airline, total) as (SELECT Airline, sum(Salary) FROM Pilot GROUP BY Airline), airlineAverage(avgSalary) as (SELECT avg(Salary) FROM Pilot ) SELECT Airline FROM totalSalary, airlineAverage WHERE totalSalary.total>airlineAverage.avgSalary;>
Вихід:
| Авіакомпанія |
|---|
| Airbus 380 |
Пояснення: Загальна зарплата всіх пілотів Airbus 380 = 298 830, а Boeing = 45 000. Середня зарплата всіх пілотів у таблиці Pilot = 57305. Оскільки лише загальна зарплата всіх пілотів Airbus 380 більша за отриману середню зарплату, тож Airbus 380 лежить у вихідному відношенні.
Важливі моменти:
- Речення SQL WITH добре використовувати зі складними операторами SQL, а не з простими
- Це також дозволяє розбивати складні запити SQL на менші, що полегшує налагодження та обробку складних запитів.
- Речення SQL WITH в основному є заміною звичайного підзапиту.