樣本資料：
CREATE TABLE Departments (
Code INTEGER PRIMARY KEY,
Name varchar(255) NOT NULL ,
Budget decimal NOT NULL
);
CREATE TABLE Employees (
SSN INTEGER PRIMARY KEY,
Name varchar(255) NOT NULL ,
LastName varchar(255) NOT NULL ,
Department INTEGER NOT NULL ,
foreign key (department) references Departments(Code)
)
INSERT INTO Departments(Code,Name,Budget) VALUES(14,'IT',65000);
INSERT INTO Departments(Code,Name,Budget) VALUES(37,'Accounting',15000);
INSERT INTO Departments(Code,Name,Budget) VALUES(59,'Human Resources',240000);
INSERT INTO Departments(Code,Name,Budget) VALUES(77,'Research',55000);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('123234877','Michael','Rogers',14);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('152934485','Anand','Manikutty',14);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('222364883','Carol','Smith',37);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('326587417','Joe','Stevens',37);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('332154719','Mary-Anne','Foster',14);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('332569843','George','O''Donnell',77);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('546523478','John','Doe',59);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('631231482','David','Smith',77);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('654873219','Zacary','Efron',59);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('745685214','Eric','Goldsmith',59);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('845657245','Elizabeth','Doe',14);
INSERT INTO Employees(SSN,Name,LastName,Department) VALUES('845657246','Kumar','Swamy',14);
問題：“選擇員工人數超過兩名的部門名稱。”
維基教科書解決方案：
/*With subquery*/
SELECT D.Name FROM Departments D
WHERE 2 <
(
SELECT COUNT(*)
FROM Employees
WHERE Department = D.Code
);
我的問題：這個解決方案如何作業？也就是說，MSSQL 如何知道Departments子查詢要保留哪些值？我看不到條件WHERE Department = D.Code可以以有用的方式將結果回傳給外部查詢的任何方式。我不認為這是僥幸，我認為我只是不明白 SQL 是如何向量化的。
uj5u.com熱心網友回復：
這稱為相關子查詢。
也就是說，內部查詢通過使用外部參考與外部查詢相關聯。在這種情況下，即D.Code。因此，子查詢被計算為每一行的D。
這不是排序的問題，實際上這個查詢可以以任何順序回傳結果。但是子查詢的結果必須大于2否則WHERE謂詞失敗。
SELECT D.Name FROM Departments D -- Departments has been aliased as D
WHERE 2 <
(
SELECT COUNT(*)
FROM Employees
WHERE Department = D.Code -- Here the inner query is being limited by
-- the reference to the outer D table
);
我可能會使用
... > 2而不是2 < ...
旁注：最好始終在子查詢中使用顯式表參考，例如
e.Department = D.Code，因為否則您可能會拼錯列并最終參考外部列而不是內部列，并且相關性將無法正常作業
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/350602.html
標籤：sql-server 子查询 矢量化
上一篇：很難找到程式集中的字符數