SELECT 範例 (Transact-SQL)
此主題提供使用 SELECT 陳述式的範例。
A. 利用 SELECT 來擷取資料列和資料行
下列範例會顯示三個程式碼範例。第一個程式碼範例會從 AdventureWorks 資料庫的 Product 資料表中，傳回所有資料列 (未指定 WHERE 子句) 和所有資料行 (使用 *)。
USE AdventureWorks;
GO
SELECT *
FROM Production.Product
ORDER BY Name ASC;
-- Alternate way.
USE AdventureWorks;
GO
SELECT p.*
FROM Production.Product AS p
ORDER BY Name ASC;
GO
這個範例會從 AdventureWorks 資料庫的 Product 資料表中，傳回所有資料列 (未指定 WHERE 子句)，但只傳回資料行子集 (Name、ProductNumber、ListPrice)。另外，也會加入一個資料行標題。
USE AdventureWorks;
GO
SELECT Name, ProductNumber, ListPrice AS Price
FROM Production.Product
ORDER BY Name ASC;
GO
這個範例只傳回產品行是 R 且製造天數小於 4 天的 Product 資料列。
USE AdventureWorks;
GO
SELECT Name, ProductNumber, ListPrice AS Price
FROM Production.Product
WHERE ProductLine = 'R'
AND DaysToManufacture < 4
ORDER BY Name ASC;
GO
B. 搭配資料行標題和計算來使用 SELECT
下列範例會傳回 Product 資料表的所有資料列。第一個範例傳回銷售總額及各項產品的折扣。第二個範例則針對各項產品來計算總收入。
USE AdventureWorks;
GO
SELECT p.Name AS ProductName,
NonDiscountSales = (OrderQty * UnitPrice),
Discounts = ((OrderQty * UnitPrice) * UnitPriceDiscount)
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY ProductName DESC;
GO
這是計算每份銷售訂單中各項產品之收入的查詢。
USE AdventureWorks;
GO
SELECT 'Total income is', ((OrderQty * UnitPrice) * (1.0 - UnitPriceDiscount)), ' for ',
p.Name AS ProductName
FROM Production.Product AS p
INNER JOIN Sales.SalesOrderDetail AS sod
ON p.ProductID = sod.ProductID
ORDER BY ProductName ASC;
GO
C. 搭配 SELECT 使用 DISTINCT
下列範例會利用 DISTINCT 來防止擷取重複的標題。
USE AdventureWorks;
GO
SELECT DISTINCT Title
FROM HumanResources.Employee
ORDER BY Title;
GO
D. 利用 SELECT INTO 來建立資料表
下列第一個範例會在 tempdb 中，建立一份名稱為 #Bicycles 的暫存資料表。若要使用這份資料表，請一律利用所顯示的確實名稱來參考它。其中包括數字符號 (#)。
USE tempdb;
IF OBJECT_ID (N'#Bicycles',N'U') IS NOT NULL
DROP TABLE #Bicycles;
GO
SELECT *
INTO #Bicycles
FROM Production.Product
WHERE ProductNumber LIKE 'BK%';
GO
以下為結果集：
name
------------------------------
#Bicycles_____________________
第二個範例會建立永久資料表 NewProducts。
USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.NewProducts', 'U') IS NOT NULL
DROP TABLE dbo.NewProducts;
GO
ALTER DATABASE AdventureWorks SET RECOVERY BULK_LOGGED;
GO
SELECT * INTO dbo.NewProducts
FROM Production.Product
WHERE ListPrice > $25
AND ListPrice < $100;
GO
ALTER DATABASE AdventureWorks SET RECOVERY FULL;
GO
以下為結果集：
name
------------------------------
NewProducts
(1 row(s) affected)
E. 使用相互關聯的子查詢
下列範例會顯示語意相等的查詢，且說明使用 EXISTS 關鍵字和 IN 關鍵字之間的差異。兩者都是有效子查詢，擷取產品模型是長袖標誌緊身內衣之各項產品名稱的單一執行個體的，Product 和 ProductModel 資料表的 ProductModelID 號碼相符。
USE AdventureWorks;
GO
SELECT DISTINCT Name
FROM Production.Product AS p
WHERE EXISTS
(SELECT *
FROM Production.ProductModel AS pm
WHERE p.ProductModelID = pm.ProductModelID
AND pm.Name = 'Long-sleeve logo jersey');
GO
-- OR
USE AdventureWorks;
GO
SELECT DISTINCT Name
FROM Production.Product
WHERE ProductModelID IN
(SELECT ProductModelID
FROM Production.ProductModel
WHERE Name = 'Long-sleeve logo jersey');
GO
下列範例在相關或重複的子查詢中使用 IN。這是一項相依於外部查詢來取得其值的查詢。這項查詢會重複執行，外部查詢可能選取的每個資料列各執行一次。這個查詢會擷取 SalesPerson 資料表中的獎金是 5000.00，且 Employee 和 SalesPerson 資料表中的員工識別碼相符的每一位員工的姓名執行個體。
USE AdventureWorks;
GO
SELECT DISTINCT c.LastName, c.FirstName
FROM Person.Contact AS c
JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID WHERE 5000.00 IN
(SELECT Bonus
FROM Sales.SalesPerson AS sp
WHERE e.EmployeeID = sp.SalesPersonID);
GO
這個陳述式中先前的子查詢無法在外部查詢之外獨立評估。它需要 Employee.EmployeeID 值，但在 SQL Server Database Engine 檢查 Employee 中的不同資料列時，這個值會跟著改變。
您也可以在外部查詢的 HAVING 子句中，使用相關的子查詢。這個範例會尋找最大標價大於模型平均值兩倍的產品模型。
USE AdventureWorks;
GO
SELECT p1.ProductModelID
FROM Production.Product AS p1
GROUP BY p1.ProductModelID
HAVING MAX(p1.ListPrice) >= ALL
(SELECT 2 * AVG(p2.ListPrice)
FROM Production.Product AS p2
WHERE p1.ProductModelID = p2.ProductModelID);
GO
這個範例利用相關的子查詢來尋找銷售了特定產品的員工名稱。
USE AdventureWorks;
GO
SELECT DISTINCT c.LastName, c.FirstName
FROM Person.Contact c JOIN HumanResources.Employee e
ON e.ContactID = c.ContactID WHERE EmployeeID IN
(SELECT SalesPersonID
FROM Sales.SalesOrderHeader
WHERE SalesOrderID IN
(SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE ProductID IN
(SELECT ProductID
FROM Production.Product p
WHERE ProductNumber = 'BK-M68B-42')));
GO
F. 使用 GROUP BY
下列範例會尋找資料庫中每份銷售訂單的總計。
USE AdventureWorks;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY SalesOrderID;
GO
由於 GROUP BY 子句，只會針對每份銷焦訂單，傳回一個包含所有銷售總和的資料行。
G. 搭配多個群組使用 GROUP BY
下列範例會依照產品識別碼和特殊優惠識別碼，來尋找平均價格和年初至今的銷售總和。
Use AdventureWorks
SELECT ProductID, SpecialOfferID, AVG(UnitPrice) AS 'Average Price',
SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY ProductID, SpecialOfferID
ORDER BY ProductID
GO
H. 使用 GROUP BY 和 WHERE
下列範例會在只擷取標價大於 $1000 的資料列之後，將結果放入群組中。
USE AdventureWorks;
GO
SELECT ProductModelID, AVG(ListPrice) AS 'Average List Price'
FROM Production.Product
WHERE ListPrice > $1000
GROUP BY ProductModelID
ORDER BY ProductModelID;
GO
I. 搭配運算式使用 GROUP BY
下列範例會依運算式來分組。如果運算式不包括彙總函式，您可以依運算式來進行分組。
USE AdventureWorks;
GO
SELECT AVG(OrderQty) AS 'Average Quantity',
NonDiscountSales = (OrderQty * UnitPrice)
FROM Sales.SalesOrderDetail
GROUP BY (OrderQty * UnitPrice)
ORDER BY (OrderQty * UnitPrice) DESC;
GO
J. 搭配 ORDER BY 使用 GROUP BY
下列範例會尋找各項產品類型的平均價格，且會依照平均價格來排序結果。
USE AdventureWorks;
GO
SELECT ProductID, AVG(UnitPrice) AS 'Average Price'
FROM Sales.SalesOrderDetail
WHERE OrderQty > 10
GROUP BY ProductID
ORDER BY AVG(UnitPrice);
GO
K. 使用 HAVING 子句
下列第一個範例會顯示含彙總函式的 HAVING 子句。它會依照產品識別碼來分組 SalesOrderDetail 資料表中的資料列，且會刪除平均訂單數量是五或更少的產品。第二個範例顯示不含彙總函式的 HAVING 子句。
USE AdventureWorks;
GO
SELECT ProductID
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID;
GO
這個查詢在 HAVING 子句中使用 LIKE 子句。
USE AdventureWorks ;
GO
SELECT SalesOrderID, CarrierTrackingNumber
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID, CarrierTrackingNumber
HAVING CarrierTrackingNumber LIKE '4BD%'
ORDER BY SalesOrderID ;
GO
L. 使用 HAVING 和 GROUP BY
下列範例會顯示如何在單一 SELECT 陳述式中使用 GROUP BY、HAVING、WHERE 和 ORDER BY 子句。它會產生群組和摘要值，但會先刪除價格超出 $25，且平均訂單數量在 5 之下的產品，再執行這個動作。它也會依 ProductID 來組織結果。
USE AdventureWorks;
GO
SELECT ProductID
FROM Sales.SalesOrderDetail
WHERE UnitPrice < 25.00
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID;
GO
M. 搭配 SUM 和 AVG 使用 HAVING
下列範例會依產品識別碼來分組 SalesOrderDetail 資料表，且只會包括訂單總計超出 $1000000.00，平均訂單數量小於 3 的產品群組。
USE AdventureWorks;
GO
SELECT ProductID, AVG(OrderQty) AS AverageQuantity, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $1000000.00
AND AVG(OrderQty) < 3;
GO
若要查看總銷售額大於 $2000000.00 的產品，請使用這項查詢：
USE AdventureWorks;
GO
SELECT ProductID, Total = SUM(LineTotal)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $2000000.00;
GO
如果您要確定每項產品的計算都至少包含了一千五百個項目，請利用 HAVING COUNT(*) > 1500 來刪除傳回銷售總計小於 1500 項的產品。這項查詢看起來如下：
USE AdventureWorks;
GO
SELECT ProductID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING COUNT(*) > 1500;
GO
N. 利用 COMPUTE BY 來計算群組總計
下列範例會利用兩個程式碼範例來顯示 COMPUTE BY 的用法。第一個程式碼範例搭配單一彙總函式使用一個 COMPUTE BY，第二個程式碼範例使用一個 COMPUTE BY 項目和兩個彙總函式。
這個查詢會計算各項產品類型價格低於 $5.00 之產品的訂單總和。
USE AdventureWorks;
GO
SELECT ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID, LineTotal
COMPUTE SUM(LineTotal) BY ProductID;
GO
這項查詢會擷取單價低於 $5.00 的產品之產品類型和訂單總計。COMPUTE BY 子句使用兩個不同的彙總函式。
USE AdventureWorks;
GO
SELECT ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID, LineTotal
COMPUTE SUM(LineTotal), MAX(LineTotal) BY ProductID;
GO
O. 利用不含 BY 的 COMPUTE 來計算總值
您可以利用不含 BY 的 COMPUTE 關鍵字來產生總計、總數等。
下列範例會尋找所有低於 $2.00 之產品類型的價格和預付款總計。
USE AdventureWorks;
GO
SELECT ProductID, OrderQty, UnitPrice, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $2.00
COMPUTE SUM(OrderQty), SUM(LineTotal);
GO
您可以在相同查詢中，使用 COMPUTE BY 和不含 BY 的 COMPUTE。下列查詢會依產品類型來尋找訂單數量的總和及產品線總計。
USE AdventureWorks;
GO
SELECT ProductID, OrderQty, UnitPrice, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID
COMPUTE SUM(OrderQty), SUM(LineTotal) BY ProductID
COMPUTE SUM(OrderQty), SUM(LineTotal);
GO
P. 計算所有資料列的計算總和
下列範例只顯示選取清單中的三個資料行，且會根據所有訂單數量和所有產品線總計，在結果尾端產生總計。
USE AdventureWorks;
GO
SELECT ProductID, OrderQty, LineTotal
FROM Sales.SalesOrderDetail
COMPUTE SUM(OrderQty), SUM(LineTotal);
GO
Q. 使用多個 COMPUTE 子句
下列範例會尋找依產品識別碼及訂單數量來組織的所有單價小於 $5 之訂單的總價，以及只依產品識別碼來組織的所有小於 $5 之訂單的總價。您可以在相同陳述式中併入多個 COMPUTE BY 子句來使用不同的彙總函式。
USE AdventureWorks;
GO
SELECT ProductID, OrderQty, UnitPrice, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID, OrderQty, LineTotal
COMPUTE SUM(LineTotal) BY ProductID, OrderQty
COMPUTE SUM(LineTotal) BY ProductID;
GO
R. 比較 GROUP BY 和 COMPUTE
下列第一個範例利用 COMPUTE 子句，依產品類型來計算產品單價低於 $5.00 之所有訂單的總和。第二個範例只利用 GROUP BY 來產生相同的摘要資訊。
USE AdventureWorks;
GO
SELECT ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID
COMPUTE SUM(LineTotal) BY ProductID;
GO
這是使用 GROUP BY 的第二項查詢。
USE AdventureWorks;
GO
SELECT ProductID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID
ORDER BY ProductID;
GO
S. 搭配 GROUP BY、COMPUTE 和 ORDER BY 子句使用 SELECT
下列範例只會傳回單價小於 $5 的訂單，然後依產品和總計來計算產品線總計。所有計算資料行都會出現在選取清單中。
USE AdventureWorks;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
COMPUTE SUM(SUM(LineTotal)) BY ProductID, OrderQty
COMPUTE SUM(SUM(LineTotal));
GO
T. 使用 INDEX 最佳化工具提示
下列範例會顯示兩種使用 INDEX 最佳化工具提示的方法。第一個範例顯示如何強制最佳化工具利用非叢集索引來擷取資料表中的資料列，第二個範例則強制利用索引 0 來掃描資料表。
-- Use the specifically named INDEX.
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, e.Title
FROM HumanResources.Employee AS e WITH (INDEX(IX_Employee_ManagerID))
JOIN Person.Contact AS c on e.ContactID = c.ContactID
WHERE ManagerID = 3;
GO
-- Force a table scan by using INDEX = 0.
USE AdventureWorks;
GO
SELECT c.LastName, c.FirstName, e.Title
FROM HumanResources.Employee AS e WITH (INDEX = 0) JOIN Person.Contact AS c
ON e.ContactID = c.ContactID
WHERE LastName = 'Johnson';
GO
U. 使用 OPTION 和 GROUP 提示
下列範例會顯示如何搭配 GROUP BY 子句來使用 OPTION (GROUP) 子句。
USE AdventureWorks;
GO
SELECT ProductID, OrderQty, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID, OrderQty
ORDER BY ProductID, OrderQty
OPTION (HASH GROUP, FAST 10);
GO
V. 使用 UNION 查詢提示
下列範例使用 MERGE UNION 查詢提示。
USE AdventureWorks;
GO
SELECT *
FROM HumanResources.Employee AS e1
UNION
SELECT *
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);
GO
W. 使用簡單 UNION
在下列範例中，結果集包括 ProductModel 和 Gloves 資料表之 ProductModelID 和 Name 資料行的內容。
USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4);
GO
-- Here is the simple union.
USE AdventureWorks;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO
X. 搭配 UNION 使用 SELECT INTO
在下列範例中，第二個 SELECT 陳述式中的 INTO 子句指定利用名稱為 ProductResults 的資料表，保留 ProductModel 和 Gloves 資料表的指定資料行之聯集的最終結果集。請注意，Gloves 資料表建立在第一個 SELECT 陳述式中。
USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.ProductResults', 'U') IS NOT NULL
DROP TABLE dbo.ProductResults;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4);
GO
USE AdventureWorks;
GO
SELECT ProductModelID, Name
INTO dbo.ProductResults
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves;
GO
SELECT *
FROM dbo.ProductResults;
Y. 搭配 ORDER BY 使用兩個 SELECT 陳述式的 UNION
搭配 UNION 子句使用之特定參數的順序非常重要。下列範例會顯示在兩個 SELECT 陳述式中的輸出重新命名一個資料行時，UNION 的正確和不正確用法。
USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.Gloves', 'U') IS NOT NULL
DROP TABLE dbo.Gloves;
GO
-- Create Gloves table.
SELECT ProductModelID, Name
INTO dbo.Gloves
FROM Production.ProductModel
WHERE ProductModelID IN (3, 4);
GO
/* INCORRECT */
USE AdventureWorks;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
ORDER BY Name
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves;
GO
/* CORRECT */
USE AdventureWorks;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO
Z. 利用三個 SELECT 陳述式的 UNION 來顯示 ALL 和括號的作用
下列範例會利用 UNION 來結合有 5 個相同資料列的三份資料表的結果。第一個範例利用 UNION ALL 來顯示重複的記錄，以及傳回所有的 15 個資料列。第二個範例利用不含 ALL 的 UNION 來刪除三個 SELECT 陳述式之組合結果中重複的資料列，並傳回 5 個資料列。
第三個範例搭配第一個 UNION 來使用 ALL，用括號括住未使用 ALL 的第二個 UNION。第二個 UNION 會先處理，因為它在括號中，且會傳回 5 個資料列，因為並未使用 ALL 選項，複本會移除。這 5 個資料列利用 SELECT 關鍵字，與第一個 UNION ALL 的結果結合起來。這並不會在兩組 5 個資料列之間移除複本。最終結果有 10 個資料列。
USE AdventureWorks;
GO
IF OBJECT_ID ('dbo.EmployeeOne', 'U') IS NOT NULL
DROP TABLE EmployeeOne;
GO
IF OBJECT_ID ('dbo.EmployeeTwo', 'U') IS NOT NULL
DROP TABLE EmployeeTwo;
GO
IF OBJECT_ID ('dbo.EmployeeThree', 'U') IS NOT NULL
DROP TABLE EmployeeThree;
GO
SELECT c.LastName, c.FirstName, e.Title
INTO dbo.EmployeeOne
FROM Person.Contact AS c JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE ManagerID = 66;
GO
SELECT c.LastName, c.FirstName, e.Title
INTO dbo.EmployeeTwo
FROM Person.Contact AS c JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE ManagerID = 66;
GO
SELECT c.LastName, c.FirstName, e.Title
INTO dbo.EmployeeThree
FROM Person.Contact AS c JOIN HumanResources.Employee AS e
ON e.ContactID = c.ContactID
WHERE ManagerID = 66;
GO
-- Union ALL
SELECT LastName, FirstName
FROM dbo.EmployeeOne
UNION ALL
SELECT LastName, FirstName
FROM dbo.EmployeeTwo
UNION ALL
SELECT LastName, FirstName
FROM dbo.EmployeeThree;
GO
SELECT LastName, FirstName
FROM dbo.EmployeeOne
UNION
SELECT LastName, FirstName
FROM dbo.EmployeeTwo
UNION
SELECT LastName, FirstName
FROM dbo.EmployeeThree;
GO
SELECT LastName, FirstName
FROM dbo.EmployeeOne
UNION ALL
(
SELECT LastName, FirstName
FROM dbo.EmployeeTwo
UNION
SELECT LastName, FirstName
FROM dbo.EmployeeThree
);
GO
請參閱