Скалярные подзапросы – это мощная функция в SQL Server, которая позволяет извлекать одно значение столбца из одной строки. Они могут использоваться в различных ситуациях, таких как выражения CASE, условия WHERE, порядок сортировки ORDER BY и выражения SELECT. В этой статье мы рассмотрим различные примеры использования скалярных подзапросов в SQL Server.
Скалярный подзапрос в выражении CASE
Давайте начнем с создания таблицы с примерами данных:
USE tempdb;
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[systems]') AND objectproperty(id, N'isusertable') = 1)
DROP TABLE [systems];
CREATE TABLE systems (id INT, dbtype VARCHAR(100));
INSERT INTO systems VALUES (1, 'sql server');
INSERT INTO systems VALUES (2, 'oracle');
INSERT INTO systems VALUES (3, 'sybase');
INSERT INTO systems VALUES (4, 'db2');
INSERT INTO systems VALUES (5, 'ingress');
INSERT INTO systems VALUES (6, 'gupta sql');
Используя скалярный подзапрос в выражении CASE, мы можем найти значения, которые являются “MS SQL Server”. Вот пример запроса:
SELECT id, dbtype,
(CASE WHEN dbtype IN (SELECT dbtype FROM systems WHERE dbtype = 'SQL Server')
THEN 'MS SQL Server'
ELSE 'Non MS SQL Server' END) AS 'SQL Server?'
FROM systems;
Вышеприведенный запрос вернет следующие результаты:
| id | dbtype | SQL Server? |
|---|---|---|
| 1 | sql server | MS SQL Server |
| 2 | oracle | Non MS SQL Server |
| 3 | sybase | Non MS SQL Server |
| 4 | db2 | Non MS SQL Server |
| 5 | ingress | Non MS SQL Server |
| 6 | gupta sql | Non MS SQL Server |
Скалярный подзапрос в операторе SELECT
Затем создадим таблицу с примерами данных:
USE tempdb;
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[employee]') AND objectproperty(id, N'isusertable') = 1)
DROP TABLE [employee];
CREATE TABLE employee (empid INT, name VARCHAR(100), salary MONEY, deptid INT);
INSERT INTO employee VALUES (1, 'claire shi', 95000.00, 1);
INSERT INTO employee VALUES (2, 'carol mok', 65000.00, 2);
INSERT INTO employee VALUES (3, 'lucy ge', 95780.00, 2);
INSERT INTO employee VALUES (4, 'william hung', 95010.00, 2);
INSERT INTO employee VALUES (5, 'chang jin', 55000.00, 1);
INSERT INTO employee VALUES (6, 'honglet hsu', 65000.00, 3);
INSERT INTO employee VALUES (7, 'karen mok', 75000.00, 1);
INSERT INTO employee VALUES (8, 'vivian shi', 98909.00, 4);
INSERT INTO employee VALUES (9, 'hsu chi', 51000.00, 3);
INSERT INTO employee VALUES (10, 'chow fat', 53000.00, 4);
В этом примере подзапрос возвращает максимальную зарплату в виде одного значения столбца. Вот запрос:
SELECT name, deptid, salary,
(SELECT MAX(salary) FROM employee me WHERE me.deptid = e.deptid) AS Department_MAX_Salary
FROM employee e
ORDER BY deptid, salary;
Вышеприведенный запрос вернет следующие результаты:
| name | deptid | salary | Department_MAX_Salary |
|---|---|---|---|
| Chang Jin | 1 | 55000 | 95000 |
| Karen Mok | 1 | 75000 | 95000 |
| Claire Shi | 1 | 95000 | 95000 |
| Carol Mok | 2 | 65000 | 95780 |
| William Hung | 2 | 95010 | 95780 |
| Lucy Ge | 2 | 95780 | 95780 |
| Hsu Chi | 3 | 51000 | 65000 |
| Honglet Hsu | 3 | 65000 | 65000 |
| Chow Fat | 4 | 53000 | 98909 |
| Vivian Shi | 4 | 98909 | 98909 |
Скалярный подзапрос в условии WHERE
Давайте создадим таблицу с примерами данных:
USE tempdb;
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[employee]') AND objectproperty(id, N'isusertable') = 1)
DROP TABLE [employee];
CREATE TABLE employee (empid INT, name VARCHAR(100), salary MONEY, deptid INT);
INSERT INTO employee VALUES (1, 'claire shi', 95000.00, 1);
INSERT INTO employee VALUES (2, 'carol mok', 65000.00, 2);
INSERT INTO employee VALUES (3, 'lucy ge', 95780.00, 2);
INSERT INTO employee VALUES (4, 'william hung', 95010.00, 2);
INSERT INTO employee VALUES (5, 'chang jin', 55000.00, 1);
INSERT INTO employee VALUES (6, 'honglet hsu', 65000.00, 3);
INSERT INTO employee VALUES (7, 'karen mok', 75000.00, 1);
INSERT INTO employee VALUES (8, 'vivian shi', 98909.00, 4);
INSERT INTO employee VALUES (9, 'hsu chi', 51000.00, 3);
INSERT INTO employee VALUES (10, 'chow fat', 53000.00, 4);
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[department]') AND objectproperty(id, N'isusertable') = 1)
DROP TABLE [department];
CREATE TABLE department (deptid INT, name VARCHAR(100));
INSERT INTO department VALUES (1, 'information technology');
INSERT INTO department VALUES (2, 'finance');
INSERT INTO department VALUES (3, 'treasury');
INSERT INTO department VALUES (4, 'marketing');
В этом примере подзапрос используется для оценки значений зарплаты основного запроса. Вот запрос:
SELECT a.name, b.name AS Department, a.salary
FROM employee a, department b
WHERE a.deptid = b.deptid
AND a.salary < (SELECT MAX(salary) FROM employee);
Вышеприведенный запрос вернет след