SELECT 範例 (Transact-SQL)

此主題提供使用 SELECT 陳述式的範例。

A.使用 SELECT 擷取資料列和資料行

下列範例會顯示三個程式碼範例。 第一個程式碼範例會從 AdventureWorks2012 資料庫的 Product 資料表中，傳回所有資料列 (未指定 WHERE 子句) 和所有資料行 (使用 *)。

USE AdventureWorks2012;
GO
SELECT *
FROM Production.Product
ORDER BY Name ASC;
-- Alternate way.
USE AdventureWorks2012;
GO
SELECT p.*
FROM Production.Product AS p
ORDER BY Name ASC;
GO

這個範例會從 AdventureWorks2012 資料庫的 Product 資料表中，傳回所有資料列 (未指定 WHERE 子句)，但只傳回資料行子集 (Name、ProductNumber、ListPrice)。 另外，也會加入一個資料行標題。

USE AdventureWorks2012;
GO
SELECT Name, ProductNumber, ListPrice AS Price
FROM Production.Product 
ORDER BY Name ASC;
GO

這個範例只傳回產品行是 R 且製造天數小於 4 天的 Product 資料列。

USE AdventureWorks2012;
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 AdventureWorks2012;
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 AdventureWorks2012;
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.使用 DISTINCT 與 SELECT

下列範例會利用 DISTINCT 來防止擷取重複的標題。

USE AdventureWorks2012;
GO
SELECT DISTINCT JobTitle
FROM HumanResources.Employee
ORDER BY JobTitle;
GO

D.使用 SELECT INTO 建立資料表

下列第一個範例會在 tempdb 中，建立一份名稱為 #Bicycles 的暫存資料表。

USE tempdb;
GO
IF OBJECT_ID (N'#Bicycles',N'U') IS NOT NULL
DROP TABLE #Bicycles;
GO
SELECT * 
INTO #Bicycles
FROM AdventureWorks2012.Production.Product
WHERE ProductNumber LIKE 'BK%';
GO

第二個範例會建立永久資料表 NewProducts。

USE AdventureWorks2012;
GO
IF OBJECT_ID('dbo.NewProducts', 'U') IS NOT NULL
    DROP TABLE dbo.NewProducts;
GO
ALTER DATABASE AdventureWorks2012 SET RECOVERY BULK_LOGGED;
GO

SELECT * INTO dbo.NewProducts
FROM Production.Product
WHERE ListPrice > $25 
AND ListPrice < $100;
GO
ALTER DATABASE AdventureWorks2012 SET RECOVERY FULL;
GO

E.使用相互關聯的子查詢

下列範例會顯示語意相等的查詢，且說明使用 EXISTS 關鍵字和 IN 關鍵字之間的差異。 兩者都是有效子查詢，擷取產品模型是長袖標誌緊身內衣之各項產品名稱的單一執行個體的，Product 和 ProductModel 資料表的 ProductModelID 號碼相符。

USE AdventureWorks2012;
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 LIKE 'Long-Sleeve Logo Jersey%');
GO

-- OR

USE AdventureWorks2012;
GO
SELECT DISTINCT Name
FROM Production.Product
WHERE ProductModelID IN
    (SELECT ProductModelID 
     FROM Production.ProductModel
     WHERE Name LIKE 'Long-Sleeve Logo Jersey%');
GO

下列範例在相關或重複的子查詢中使用 IN。 這是一項相依於外部查詢來取得其值的查詢。 這項查詢會重複執行，外部查詢可能選取的每個資料列各執行一次。 這個查詢會擷取 SalesPerson 資料表中的獎金是 5000.00，且 Employee 和 SalesPerson 資料表中的員工識別碼相符的每一位員工的姓名執行個體。

USE AdventureWorks2012;
GO
SELECT DISTINCT p.LastName, p.FirstName 
FROM Person.Person AS p 
JOIN HumanResources.Employee AS e
    ON e.BusinessEntityID = p.BusinessEntityID WHERE 5000.00 IN
    (SELECT Bonus
     FROM Sales.SalesPerson AS sp
     WHERE e.BusinessEntityID = sp.BusinessEntityID);
GO

這個陳述式中先前的子查詢無法在外部查詢之外獨立評估。 它需要 Employee.EmployeeID 值，但在 SQL Server Database Engine 檢查 Employee 中的不同資料列時，這個值會跟著改變。

您也可以在外部查詢的 HAVING 子句中，使用相關的子查詢。 這個範例會尋找最大標價大於模型平均值兩倍的產品模型。

USE AdventureWorks2012;
GO
SELECT p1.ProductModelID
FROM Production.Product AS p1
GROUP BY p1.ProductModelID
HAVING MAX(p1.ListPrice) >= ALL
    (SELECT AVG(p2.ListPrice)
     FROM Production.Product AS p2
     WHERE p1.ProductModelID = p2.ProductModelID);
GO

這個範例利用相關的子查詢來尋找銷售了特定產品的員工名稱。

USE AdventureWorks2012;
GO
SELECT DISTINCT pp.LastName, pp.FirstName 
FROM Person.Person pp JOIN HumanResources.Employee e
ON e.BusinessEntityID = pp.BusinessEntityID WHERE pp.BusinessEntityID 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 AdventureWorks2012;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY SalesOrderID;
GO

由於 GROUP BY 子句，只會針對每份銷焦訂單，傳回一個包含所有銷售總和的資料行。

G.使用 GROUP BY 與多個群組

下列範例會依照產品識別碼和特殊優惠識別碼，來尋找平均價格和年初至今的銷售總和。

USE AdventureWorks2012;
GO
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 AdventureWorks2012;
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 AdventureWorks2012;
GO
SELECT AVG(OrderQty) AS [Average Quantity], 
NonDiscountSales = (OrderQty * UnitPrice)
FROM Sales.SalesOrderDetail
GROUP BY (OrderQty * UnitPrice)
ORDER BY (OrderQty * UnitPrice) DESC;
GO

J.使用 GROUP BY 與 ORDER BY

下列範例會尋找各項產品類型的平均價格，且會依照平均價格來排序結果。

USE AdventureWorks2012;
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 AdventureWorks2012;
GO
SELECT ProductID 
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID;
GO

這個查詢在 HAVING 子句中使用 LIKE 子句。

USE AdventureWorks2012 ;
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 AdventureWorks2012;
GO
SELECT ProductID 
FROM Sales.SalesOrderDetail
WHERE UnitPrice < 25.00
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID;
GO

M.使用 HAVING 與 SUM 及 AVG

下列範例會依產品識別碼來分組 SalesOrderDetail 資料表，且只會包括訂單總計超出 $1000000.00，平均訂單數量小於 3 的產品群組。

USE AdventureWorks2012;
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 AdventureWorks2012;
GO
SELECT ProductID, Total = SUM(LineTotal)
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING SUM(LineTotal) > $2000000.00;
GO

如果您要確定每項產品的計算都至少包含了一千五百個項目，請利用 HAVING COUNT(*) > 1500 來刪除傳回銷售總計小於 1500 項的產品。 查詢內容如下所示：

USE AdventureWorks2012;
GO
SELECT ProductID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING COUNT(*) > 1500;
GO

N.使用 INDEX 最佳化工具提示

下列範例會顯示兩種使用 INDEX 最佳化工具提示的方法。 第一個範例顯示如何強制最佳化工具利用非叢集索引來擷取資料表中的資料列，第二個範例則強制利用索引 0 來掃描資料表。

USE AdventureWorks2012;
GO
SELECT pp.FirstName, pp.LastName, e.NationalIDNumber
FROM HumanResources.Employee AS e WITH (INDEX(AK_Employee_NationalIDNumber))
JOIN Person.Person AS pp on e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO

-- Force a table scan by using INDEX = 0.
USE AdventureWorks2012;
GO
SELECT pp.LastName, pp.FirstName, e.JobTitle
FROM HumanResources.Employee AS e WITH (INDEX = 0) JOIN Person.Person AS pp
ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO

M.使用 OPTION 與 GROUP 提示

下列範例會顯示如何搭配 GROUP BY 子句來使用 OPTION (GROUP) 子句。

USE AdventureWorks2012;
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

O.使用 UNION 查詢提示

下列範例使用 MERGE UNION 查詢提示。

USE AdventureWorks2012;
GO
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours
FROM HumanResources.Employee AS e1
UNION
SELECT BusinessEntityID, JobTitle, HireDate, VacationHours, SickLeaveHours
FROM HumanResources.Employee AS e2
OPTION (MERGE UNION);
GO

P.使用簡單 UNION

在下列範例中，結果集包括 ProductModel 和 Gloves 資料表之 ProductModelID 和 Name 資料行的內容。

USE AdventureWorks2012;
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 AdventureWorks2012;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO

Q.搭配 UNION 使用 SELECT INTO

在下列範例中，第二個 SELECT 陳述式中的 INTO 子句指定利用名稱為 ProductResults 的資料表，保留 ProductModel 和 Gloves 資料表的指定資料行之聯集的最終結果集。 請注意，Gloves 資料表建立在第一個 SELECT 陳述式中。

USE AdventureWorks2012;
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 AdventureWorks2012;
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 ProductModelID, Name 
FROM dbo.ProductResults;

R.搭配 ORDER BY 使用兩個 SELECT 陳述式的 UNION

搭配 UNION 子句使用之特定參數的順序非常重要。 下列範例會顯示在兩個 SELECT 陳述式中的輸出重新命名一個資料行時，UNION 的正確和不正確用法。

USE AdventureWorks2012;
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 AdventureWorks2012;
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 AdventureWorks2012;
GO
SELECT ProductModelID, Name
FROM Production.ProductModel
WHERE ProductModelID NOT IN (3, 4)
UNION
SELECT ProductModelID, Name
FROM dbo.Gloves
ORDER BY Name;
GO

S.利用三個 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 AdventureWorks2012;
GO
IF OBJECT_ID ('dbo.EmployeeOne', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeOne;
GO
IF OBJECT_ID ('dbo.EmployeeTwo', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeTwo;
GO
IF OBJECT_ID ('dbo.EmployeeThree', 'U') IS NOT NULL
DROP TABLE dbo.EmployeeThree;
GO

SELECT pp.LastName, pp.FirstName, e.JobTitle 
INTO dbo.EmployeeOne
FROM Person.Person AS pp JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO
SELECT pp.LastName, pp.FirstName, e.JobTitle 
INTO dbo.EmployeeTwo
FROM Person.Person AS pp JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO
SELECT pp.LastName, pp.FirstName, e.JobTitle 
INTO dbo.EmployeeThree
FROM Person.Person AS pp JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = pp.BusinessEntityID
WHERE LastName = 'Johnson';
GO
-- Union ALL
SELECT LastName, FirstName, JobTitle
FROM dbo.EmployeeOne
UNION ALL
SELECT LastName, FirstName ,JobTitle
FROM dbo.EmployeeTwo
UNION ALL
SELECT LastName, FirstName,JobTitle 
FROM dbo.EmployeeThree;
GO

SELECT LastName, FirstName,JobTitle
FROM dbo.EmployeeOne
UNION 
SELECT LastName, FirstName, JobTitle 
FROM dbo.EmployeeTwo
UNION 
SELECT LastName, FirstName, JobTitle 
FROM dbo.EmployeeThree;
GO

SELECT LastName, FirstName,JobTitle 
FROM dbo.EmployeeOne
UNION ALL
(
SELECT LastName, FirstName, JobTitle 
FROM dbo.EmployeeTwo
UNION
SELECT LastName, FirstName, JobTitle 
FROM dbo.EmployeeThree
);
GO

請參閱

參考

CREATE TRIGGER (Transact-SQL)

CREATE VIEW (Transact-SQL)

DELETE (Transact-SQL)

EXECUTE (Transact-SQL)

運算式 (Transact-SQL)

INSERT (Transact-SQL)

LIKE (Transact-SQL)

UNION (Transact-SQL)

EXCEPT 和 INTERSECT (Transact-SQL)

UPDATE (Transact-SQL)

WHERE (Transact-SQL)

PathName (Transact-SQL)

INTO 子句 (Transact-SQL)