SELECT 示例 (Transact-SQL)

本主题将提供使用 SELECT 语句的示例。

A. 使用 SELECT 检索行和列

以下示例显示三个代码示例。第一个代码示例返回 AdventureWorks2008R2 数据库的 Product 表中的所有行（未指定 WHERE 子句）和所有列（使用了 *）。

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

该示例返回 AdventureWorks2008R2 数据库的 Product 表的所有行（未指定 WHERE 子句）和列子集（Name、ProductNumber、ListPrice）。此外，还添加了一个列标题。

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

该示例仅返回 Product 表中产品系列为 R 且生产天数少于 4 的那些行。

USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2.Production.Product
WHERE ProductNumber LIKE 'BK%';
GO

第二个示例创建永久表 NewProducts。

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

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

E. 使用相关子查询

以下示例显示了语义等价的查询并说明了使用 EXISTS 关键字和 IN 关键字的区别。两个都是有效子查询示例，用于检索产品型号为长袖标志运动衫且 ProductModelID 编号在 Product 和 ProductModel 两个表中相匹配的每种产品名称的实例。

USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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.BusinessEntityID 值，但是该值随着 SQL Server 数据库引擎检查的 Employee 中的不同行而发生改变。

相关子查询还可以用于外部查询的 HAVING 子句。以下示例查找其最高标价高于其平均标价两倍以上的产品型号。

USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
GO
SELECT SalesOrderID, SUM(LineTotal) AS SubTotal
FROM Sales.SalesOrderDetail
GROUP BY SalesOrderID
ORDER BY SalesOrderID;
GO

由于使用了 GROUP BY 子句，因此针对每个销售订单只返回一行销售总额。

G. 对多个组使用 GROUP BY

以下示例查找按产品 ID 和特价产品 ID 分组的平均价格和迄今为止的年销售总额。

USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 子句。该子句按产品 ID 将 SalesOrderDetail 表中的行进行分组并消除那些平均订单数量等于或小于五的产品。第二个示例显示不带聚合函数的 HAVING 子句。

USE AdventureWorks2008R2;
GO
SELECT ProductID 
FROM Sales.SalesOrderDetail
GROUP BY ProductID
HAVING AVG(OrderQty) > 5
ORDER BY ProductID;
GO

该查询在 HAVING 子句中使用 LIKE 子句。

USE AdventureWorks2008R2 ;
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 AdventureWorks2008R2;
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 一起使用

以下示例按产品 ID 将 SalesOrderDetail 表进行分组，结果中仅包含订单总金额超过 $1000000.00 且其平均订单数量少于 3 的产品的组。

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

若要确保在计算中至少为每种产品包含 1500 项，请使用 HAVING COUNT(*) > 1500 消除返回总数少于 1500 售出项的产品。该查询如下所示：

USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
GO
SELECT ProductID, OrderQty, UnitPrice, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $2.00
COMPUTE SUM(OrderQty), SUM(LineTotal);
GO

在同一查询中可以使用 COMPUTE BY 和不带 BY 的 COMPUTE。以下查询按产品类型查找订单数量总和与行总计，然后再计算订单数量总计和行总计。

USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
GO
SELECT ProductID, OrderQty, LineTotal
FROM Sales.SalesOrderDetail
COMPUTE SUM(OrderQty), SUM(LineTotal);
GO

Q. 使用多个 COMPUTE 子句

以下示例查找按产品 ID 和订单数量组织的单价小于 $5 的所有订单的价格总和，以及只按产品 ID 组织的单价小于 $5 的所有订单的价格总和。通过包含多个 COMPUTE BY 子句，可以在同一语句中使用不同的聚合函数。

USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
GO
SELECT ProductID, LineTotal
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
ORDER BY ProductID
COMPUTE SUM(LineTotal) BY ProductID;
GO

下面是使用 GROUP BY 的第二个查询示例。

USE AdventureWorks2008R2;
GO
SELECT ProductID, SUM(LineTotal) AS Total
FROM Sales.SalesOrderDetail
WHERE UnitPrice < $5.00
GROUP BY ProductID
ORDER BY ProductID;
GO

S. SELECT 与 GROUP BY、COMPUTE 和 ORDER BY 子句一起使用

以下示例仅返回那些单价低于 $5 的订单，然后再按产品计算行合计，然后计算总计。所有的计算列都出现在选择列表中。

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

U. 使用 OPTION 和 GROUP 提示

以下示例说明了如何将 OPTION (GROUP) 子句与 GROUP BY 子句一起使用。

USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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. 将 SELECT INTO 与 UNION 一起使用

在以下示例中，第二个 SELECT 语句中的 INTO 子句指定名为 ProductResults 的表保存 ProductModel 和 Gloves 表中的指定列的并集（最终结果集）。注意，Gloves 表是由第一个 SELECT 语句创建的。

USE AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 AdventureWorks2008R2;
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 行。

第三个示例将 ALL 用于第一个 UNION，并用括号将第二个没有使用 ALL 的 UNION 括起来。第二个 UNION 因位于括号内而首先得到处理，并且因为没有使用 ALL 选项，所以重复行被删除而返回 5 行。通过使用 UNION ALL 关键字将这 5 行与第一个 SELECT 的结果组合在一起。这不会删除两个 5 行结果集之间的重复行。最终结果有 10 行。

USE AdventureWorks2008R2;
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)

概念

分布式查询

子查询基础知识

使用变量和参数（数据库引擎）