通过

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

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

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

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

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

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 子句。 该子句按产品 ID 将 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 一起使用

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

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

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

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

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

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