SELECT 示例 (Transact-SQL)

SELECT 示例 (Transact-SQL)

开启低带宽视图

社区内容

本节内容

统计批注 (0)

单击此处以展开

全部折叠

语言筛选器

同时提供下列产品的其他版本：

SQL Server 2005

SQL Server 2008 联机丛书（2009 年 5 月）

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

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

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. 将 DISTINCT 与 SELECT 一起使用

以下示例使用 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 关键字的区别。两个都是有效子查询示例，用于检索产品型号为长袖标志运动衫且 ProductModelID 编号在 Product 和 ProductModel 两个表中相匹配的每种产品名称的实例。

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 数据库引擎检查的 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

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

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. 将 GROUP BY 与 ORDER 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 子句。该子句按产品 ID 将 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. 将 HAVING 与 SUM 和 AVG 一起使用

以下示例按产品 ID 将 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

若要确保在计算中至少为每种产品包含 1500 项，请使用 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 子句

以下示例查找按产品 ID 和订单数量组织的单价小于 $5 的所有订单的价格总和，以及只按产品 ID 组织的单价小于 $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. SELECT 与 GROUP BY、COMPUTE 和 ORDER BY 子句一起使用

以下示例仅返回那些单价低于 $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 提示

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

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

在以下示例中，第二个 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 行。

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

请参阅

标记

: 添加标记

社区内容

添加新内容

批注

个人信息中心 | 联系我们 | 中文速递邮件

参考

其他资源

帮助和信息