SELECT (Transact-SQL)SELECT (Transact-SQL)

适用对象: yesSQL ServeryesAzure SQL 数据库yesAzure SQL 数据仓库yes并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

从数据库中检索行,并允许从 SQL ServerSQL Server中的一个或多个表中选择一个或多个行或列。Retrieves rows from the database and enables the selection of one or many rows or columns from one or many tables in SQL ServerSQL Server. 虽然 SELECT 语句的完整语法较复杂,但其主要子句可归纳如下:The full syntax of the SELECT statement is complex, but the main clauses can be summarized as:

[ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> ] } ][ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> ] } ]

SELECT select_list [ INTO new_table ] SELECT select_list [ INTO new_table ]

[ FROM table_source ] [ WHERE search_condition ] [ FROM table_source ] [ WHERE search_condition ]

[ GROUP BY group_by_expression ] [ GROUP BY group_by_expression ]

[ HAVING search_condition ] [ HAVING search_condition ]

[ ORDER BY order_expression [ ASC | DESC ] ] [ ORDER BY order_expression [ ASC | DESC ] ]

可在查询之间使用 UNION、EXCEPT 和 INTERSECT 运算符,以便将各个查询的结果合并或比较到一个结果集中。The UNION, EXCEPT, and INTERSECT operators can be used between queries to combine or compare their results into one result set.

主题链接图标 TRANSACT-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
<SELECT statement> ::=    
    [ WITH { [ XMLNAMESPACES ,] [ <common_table_expression> [,...n] ] } ]  
    <query_expression>   
    [ ORDER BY { order_by_expression | column_position [ ASC | DESC ] }   
  [ ,...n ] ]   
    [ <FOR Clause>]   
    [ OPTION ( <query_hint> [ ,...n ] ) ]   
<query_expression> ::=   
    { <query_specification> | ( <query_expression> ) }   
    [  { UNION [ ALL ] | EXCEPT | INTERSECT }  
        <query_specification> | ( <query_expression> ) [...n ] ]   
<query_specification> ::=   
SELECT [ ALL | DISTINCT ]   
    [TOP ( expression ) [PERCENT] [ WITH TIES ] ]   
    < select_list >   
    [ INTO new_table ]   
    [ FROM { <table_source> } [ ,...n ] ]   
    [ WHERE <search_condition> ]   
    [ <GROUP BY> ]   
    [ HAVING < search_condition > ]   
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  
  
[ WITH <common_table_expression> [ ,...n ] ]  
SELECT <select_criteria>  
[;]  
  
<select_criteria> ::=  
    [ TOP ( top_expression ) ]   
    [ ALL | DISTINCT ]   
    { * | column_name | expression } [ ,...n ]   
    [ FROM { table_source } [ ,...n ] ]  
    [ WHERE <search_condition> ]   
    [ GROUP BY <group_by_clause> ]   
    [ HAVING <search_condition> ]   
    [ ORDER BY <order_by_expression> ]  
    [ OPTION ( <query_option> [ ,...n ] ) ]  
  

RemarksRemarks

由于 SELECT 语句的复杂性,下面按子句说明详细的语法元素和参数:Because of the complexity of the SELECT statement, detailed syntax elements and arguments are shown by clause:

WITH XMLNAMESPACESWITH XMLNAMESPACES

WITH common_table_expressionWITH common_table_expression
HAVINGHAVING
SELECT 子句SELECT Clause UNIONUNION
INTO 子句INTO Clause EXCEPT 和 INTERSECTEXCEPT and INTERSECT
FROMFROM ORDER BYORDER BY
WHEREWHERE FOR 子句FOR Clause
GROUP BYGROUP BY OPTION 子句OPTION Clause

SELECT 语句中的子句顺序非常重要。The order of the clauses in the SELECT statement is significant. 可以省略可选子句,但这些子句在使用时必须按适当的顺序出现。Any one of the optional clauses can be omitted, but when the optional clauses are used, they must appear in the appropriate order.

只有当这些语句的选择列表包含给函数的局部变量赋值的表达式时,在用户定义函数中才允许有 SELECT 语句。SELECT statements are permitted in user-defined functions only if the select lists of these statements contain expressions that assign values to variables that are local to the functions.

对于由四部分组成的名称,若其中的服务器名称使用的是 OPENDATASOURCE 函数,则该名称可以在表名能够在 SELECT 语句内出现的任何位置作为表源使用。A four-part name constructed with the OPENDATASOURCE function as the server-name part can be used as a table source wherever a table name can appear in a SELECT statement. 无法为 Azure SQL DatabaseAzure SQL Database 指定由四部分组成的名称。A four-part name cannot be specified for Azure SQL DatabaseAzure SQL Database.

某些应用于 SELECT 语句的语法限制涉及到远程表。Some syntax restrictions apply to SELECT statements that involve remote tables.

SELECT 语句的逻辑处理顺序Logical Processing Order of the SELECT statement

以下步骤显示 SELECT 语句的逻辑处理顺序(即绑定顺序)。The following steps show the logical processing order, or binding order, for a SELECT statement. 此顺序确定在一个步骤中定义的对象何时可用于后续步骤中的子句。This order determines when the objects defined in one step are made available to the clauses in subsequent steps. 例如,如果查询处理器可以绑定到(访问)在 FROM 子句中定义的表或视图,则这些对象及其列可用于所有后续步骤。For example, if the query processor can bind to (access) the tables or views defined in the FROM clause, these objects and their columns are made available to all subsequent steps. 相反,因为 SELECT 子句处于步骤 8,所以,在该子句中定义的任何列别名或派生列都无法被之前的子句引用。Conversely, because the SELECT clause is step 8, any column aliases or derived columns defined in that clause cannot be referenced by preceding clauses. 不过,它们可由 ORDER BY 子句之类的后续子句引用。However, they can be referenced by subsequent clauses such as the ORDER BY clause. 该语句的实际物理执行由查询处理器确定,因此顺序可能与此列表不同。The actual physical execution of the statement is determined by the query processor and the order may vary from this list.

  1. FROMFROM
  2. ONON
  3. JOINJOIN
  4. WHEREWHERE
  5. GROUP BYGROUP BY
  6. WITH CUBE 或 WITH ROLLUPWITH CUBE or WITH ROLLUP
  7. HAVINGHAVING
  8. SELECTSELECT
  9. DISTINCTDISTINCT
  10. ORDER BYORDER BY
  11. 返回页首TOP

警告

上述顺序是常规顺序。The preceding sequence is usually true. 但是仍然存在不同于此顺序的少见情况。However, there are uncommon cases where the sequence may differ.

例如,假设视图中具有聚集索引,该视图排除了一些表行,并且视图的 SELECT 列列表使用可将数据类型从 varchar 更改为 integer 的 CONVERT 。For example, suppose you have a clustered index on a view, and the view excludes some table rows, and the view's SELECT column list uses a CONVERT that changes a data type from varchar to integer. 在此情况下,CONVERT 可能会先于 WHERE 语句执行。In this situation, the CONVERT may execute before the WHERE clause executes. 这种情况确实少见。Uncommon indeed. 如果需要解决此问题,通常可通过某种方法修改视图来避免出现顺序不同的情况。Often there is a way to modify your view to avoid the different sequence, if it matters in your case.

权限Permissions

选择数据要求对表或视图具有 SELECT 权限,该权限可从更高级别的权限继承,例如针对架构的 SELECT 权限或针对表的 CONTROL 权限。Selecting data requires SELECT permission on the table or view, which could be inherited from a higher scope such as SELECT permission on the schema or CONTROL permission on the table. 或者要求具有 db_datareader 或 db_owner 固定数据库角色或 sysadmin 固定服务器角色的成员身份 。Or requires membership in the db_datareader or db_owner fixed database roles, or the sysadmin fixed server role. 使用 SELECTINTO 创建新表既要求 CREATETABLE 权限,还要求拥有新表的架构上的 ALTERSCHEMA 权限 。Creating a new table using SELECTINTO also requires both the CREATETABLE permission, and the ALTERSCHEMA permission on the schema that owns the new table.

示例:Examples:

下面的示例使用 AdventureWorksPDW2012AdventureWorksPDW2012 数据库。The following examples use the AdventureWorksPDW2012AdventureWorksPDW2012 database.

A.A. 使用 SELECT 检索行和列Using SELECT to retrieve rows and columns

本部分演示三个代码示例。This section shows three code examples. 此第一个代码示例返回 DimEmployee 表中的所有行(未指定 WHERE 子句)和所有列(使用了 *)。This first code example returns all rows (no WHERE clause is specified) and all columns (using the *) from the DimEmployee table.

SELECT *  
FROM DimEmployee  
ORDER BY LastName;  

此第二个示例使用表别名实现相同结果。This next example using table aliasing to achieve the same result.

SELECT e.*  
FROM DimEmployee AS e  
ORDER BY LastName;  

此示例返回 AdventureWorksPDW2012 数据库的 DimEmployee 表中的所有行(未指定 WHERE 子句)以及列(FirstNameLastNameStartDate)的子集。This example returns all rows (no WHERE clause is specified) and a subset of the columns (FirstName, LastName, StartDate) from the DimEmployee table in the AdventureWorksPDW2012 database. 第三个列标题重命名为 FirstDayThe third column heading is renamed to FirstDay.

SELECT FirstName, LastName, StartDate AS FirstDay  
FROM DimEmployee   
ORDER BY LastName;  

此示例仅返回 DimEmployee 的行,其中包含非 NULL 的 EndDate 和为“M”(已婚)的 MaritalStatusThis example returns only the rows for DimEmployee that have an EndDate that is not NULL and a MaritalStatus of 'M' (married).

SELECT FirstName, LastName, StartDate AS FirstDay  
FROM DimEmployee   
WHERE EndDate IS NOT NULL   
AND MaritalStatus = 'M'  
ORDER BY LastName;  

B.B. 将 SELECT 与列标题和列计算一起使用Using SELECT with column headings and calculations

下面的示例返回 DimEmployee 表中的所有行,并基于每位员工的 BaseRate 和 40 小时工作周计算他们的总工资。The following example returns all rows from the DimEmployee table, and calculates the gross pay for each employee based on their BaseRate and a 40-hour work week.

SELECT FirstName, LastName, BaseRate, BaseRate * 40 AS GrossPay  
FROM DimEmployee  
ORDER BY LastName;  

C.C. 将 DISTINCT 与 SELECT 一起使用Using DISTINCT with SELECT

下面的示例使用 DISTINCTDimEmployee 表中的所有唯一标题生成列表。The following example uses DISTINCT to generate a list of all unique titles in the DimEmployee table.

SELECT DISTINCT Title  
FROM DimEmployee  
ORDER BY Title;  

D.D. 使用 GROUP BYUsing GROUP BY

下面的示例查找每天所有销售的总金额。The following example finds the total amount for all sales on each day.

SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY OrderDateKey  
ORDER BY OrderDateKey;  

由于使用了 GROUP BY 子句,因此每天只返回一行销售总额。Because of the GROUP BY clause, only one row containing the sum of all sales is returned for each day.

E.E. 对多个组使用 GROUP BYUsing GROUP BY with multiple groups

下面的示例查找平均价格和每天的互联网销售总额(按订单日期和促销关键字进行分组)。The following example finds the average price and the sum of Internet sales for each day, grouped by order date and the promotion key.


SELECT OrderDateKey, PromotionKey, AVG(SalesAmount) AS AvgSales, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY OrderDateKey, PromotionKey  
ORDER BY OrderDateKey;   

F.F. 使用 GROUP BY 和 WHEREUsing GROUP BY and WHERE

下面的示例在只检索订单日期晚于 2002 年 8 月 1 日的行后对结果进行分组。The following example puts the results into groups after retrieving only the rows with order dates later than August 1, 2002.

SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
WHERE OrderDateKey > '20020801'  
GROUP BY OrderDateKey  
ORDER BY OrderDateKey;  

G.G. 将 GROUP BY 与表达式一起使用Using GROUP BY with an expression

以下示例按表达式进行分组。The following example groups by an expression. 如果表达式不包含聚合函数,则可以按表达式进行分组。You can group by an expression if the expression does not include aggregate functions.

SELECT SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY (OrderDateKey * 10);  

H.H. 将 GROUP BY 与 ORDER BY 一起使用Using GROUP BY with ORDER BY

下面的示例查找每天的总销售额以及当天的订单。The following example finds the sum of sales per day, and orders by the day.

SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY OrderDateKey  
ORDER BY OrderDateKey;  

I.I. 使用 HAVING 子句Using the HAVING clause

此查询使用 HAVING 子句限制结果。This query uses the HAVING clause to restrict results.

SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales  
FROM FactInternetSales  
GROUP BY OrderDateKey  
HAVING OrderDateKey > 20010000  
ORDER BY OrderDateKey;  

另请参阅See Also

SELECT 示例 (Transact-SQL)SELECT Examples (Transact-SQL)
提示 (Transact-SQL)Hints (Transact-SQL)