SELECT 子句 (Transact-SQL)SELECT Clause (Transact-SQL)

适用对象:是SQL Server 是Azure SQL 数据库 否Azure Synapse Analytics (SQL DW) 否并行数据仓库APPLIES TO: yesSQL Server yesAzure SQL Database noAzure Synapse Analytics (SQL DW) noParallel Data Warehouse

指定查询返回的列。Specifies the columns to be returned by the query.

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

语法Syntax

  
SELECT [ ALL | DISTINCT ]  
[ TOP ( expression ) [ PERCENT ] [ WITH TIES ] ]   
<select_list>   
<select_list> ::=   
    {   
      *   
      | { table_name | view_name | table_alias }.*   
      | {  
          [ { table_name | view_name | table_alias }. ]  
               { column_name | $IDENTITY | $ROWGUID }   
          | udt_column_name [ { . | :: } { { property_name | field_name }   
            | method_name ( argument [ ,...n] ) } ]  
          | expression  
          [ [ AS ] column_alias ]   
         }  
      | column_alias = expression   
    } [ ,...n ]   

参数Arguments

ALLALL
指定在结果集中可以包含重复行。Specifies that duplicate rows can appear in the result set. ALL 为默认值。ALL is the default.

DISTINCTDISTINCT
指定在结果集中只能包含唯一行。Specifies that only unique rows can appear in the result set. 对于 DISTINCT 关键字来说,Null 值是相等的。Null values are considered equal for the purposes of the DISTINCT keyword.

TOP (expression ) [ PERCENT ] [ WITH TIES ] TOP (expression ) [ PERCENT ] [ WITH TIES ]
指示只能从查询结果集返回指定的第一组行或指定的百分比数目的行。Indicates that only a specified first set or percent of rows will be returned from the query result set. expression 可以是行数或行的百分比。expression can be either a number or a percent of the rows.

为了能够向后兼容,支持在 SELECT 语句中使用不带括号的 TOP expression,但推荐不这样做 。For backward compatibility, using the TOP expression without parentheses in SELECT statements is supported, but we do not recommend it. 有关详细信息,请参阅 TOP (Transact-SQL)For more information, see TOP (Transact-SQL).

< select_list > 要为结果集选择的列。< select_list > The columns to be selected for the result set. 选择列表是以逗号分隔的一系列表达式。The select list is a series of expressions separated by commas. 可在选择列表中指定的表达式的最大数目是 4096。The maximum number of expressions that can be specified in the select list is 4096.

*
指定返回 FROM 子句中的所有表和视图中的所有列。Specifies that all columns from all tables and views in the FROM clause should be returned. 这些列按 FROM 子句中指定的表或视图顺序返回,并对应于它们在表或视图中的顺序。The columns are returned by table or view, as specified in the FROM clause, and in the order in which they exist in the table or view.

table_name | view_name | table_alias.* table_name | view_name | table_alias.*
将 * 的作用域限制为指定的表或视图。Limits the scope of the * to the specified table or view.

column_name column_name
要返回的列名。Is the name of a column to return. 请限定 column_name 以避免引用不明确,例如,当 FROM 子句中的两个表包含同名的列时会出现这种情况 。Qualify column_name to prevent an ambiguous reference, such as occurs when two tables in the FROM clause have columns with duplicate names. 例如,AdventureWorks2012AdventureWorks2012 数据库中的 SalesOrderHeader 和 SalesOrderDetail 表中均有名为 ModifiedDate 的列。For example, the SalesOrderHeader and SalesOrderDetail tables in the AdventureWorks2012AdventureWorks2012 database both have a column named ModifiedDate. 如果将这两个表加入查询,则可以在选择列表中指定 SalesOrderDetail 项的修改日期,即 SalesOrderDetail.ModifiedDat。If the two tables are joined in a query, the modified date of the SalesOrderDetail entries can be specified in the select list as SalesOrderDetail.ModifiedDate.

expressionexpression
常量、函数以及由一个或多个运算符连接的列名、常量和函数的任意组合,或者是子查询。Is a constant, function, any combination of column names, constants, and functions connected by an operator or operators, or a subquery.

$IDENTITY$IDENTITY
返回标识列。Returns the identity column. 有关详细信息,请参阅 IDENTITY(属性)(Transact-SQL)ALTER TABLE (Transact-SQL)CREATE TABLE (Transact-SQL)For more information, see IDENTITY (Property) (Transact-SQL), ALTER TABLE (Transact-SQL), and CREATE TABLE (Transact-SQL).

如果 FROM 子句中的多个表内都包含具有 IDENTITY 属性的列,则必须使用特定的表名限定 $IDENTITY(如 T1.$IDENTITY)。If more than one table in the FROM clause has a column with the IDENTITY property, $IDENTITY must be qualified with the specific table name, such as T1.$IDENTITY.

$ROWGUID$ROWGUID
返回行 GUID 列。Returns the row GUID column.

如果在 FROM 子句中有多个表具有 ROWGUIDCOL 属性,则必须用特定的表名限定 $ROWGUID,如 T1.$ROWGUID。If there is more than one table in the FROM clause with the ROWGUIDCOL property, $ROWGUID must be qualified with the specific table name, such as T1.$ROWGUID.

udt_column_name udt_column_name
要返回的公共语言运行时 (CLR) 用户定义类型列的名称。Is the name of a common language runtime (CLR) user-defined type column to return.

备注

SQL Server Management StudioSQL Server Management Studio 返回以二进制表示形式表示的用户定义类型值。returns user-defined type values in binary representation. 若要以字符串或 XML 格式返回用户定义类型值,请使用 CASTCONVERTTo return user-defined type values in string or XML format, use CAST or CONVERT.

{ .{ . | :: }| :: }
指定 CLR 用户定义类型的方法、属性或字段。Specifies a method, property, or field of a CLR user-defined type. 使用 .Use . 用于实例(非静态)方法、属性或字段。for an instance (nonstatic) method, property, or field. 将 :: 用于静态方法、属性或字段。Use :: for a static method, property, or field. 若要调用 CLR 用户定义类型的方法、属性或字段,必须对类型具有 EXECUTE 权限。To invoke a method, property, or field of a CLR user-defined type, you must have EXECUTE permission on the type.

property_name property_name
udt_column_name 的公共属性 。Is a public property of udt_column_name.

field_name field_name
udt_column_name 的公共数据成员 。Is a public data member of udt_column_name.

method_name method_name
带一个或多个参数的 udt_column_name 的公共方法 。Is a public method of udt_column_name that takes one or more arguments. method_name 不能是赋值函数方法 。method_name cannot be a mutator method.

以下示例通过调用名为 Location 类型的方法,从 point 表中选择 Cities 列的值(定义为 Distance 类型):The following example selects the values for the Location column, defined as type point, from the Cities table, by invoking a method of the type called Distance:

CREATE TABLE dbo.Cities (  
     Name varchar(20),  
     State varchar(20),  
     Location point );  
GO  
DECLARE @p point (32, 23), @distance float;  
GO  
SELECT Location.Distance (@p)  
FROM Cities;  

column_ alias column_ alias
查询结果集内替换列名的可选名。Is an alternative name to replace the column name in the query result set. 例如,可以为名为 quantity 的列指定别名,如 Quantity、Quantity to Date 或 Qty。For example, an alias such as Quantity, or Quantity to Date, or Qty can be specified for a column named quantity.

别名还可用于为表达式结果指定名称,例如:Aliases are used also to specify names for the results of expressions, for example:

USE AdventureWorks2012;  
GO  
SELECT AVG(UnitPrice) AS [Average Price]  
FROM Sales.SalesOrderDetail;

column_alias 可以用于 ORDER BY 子句 。column_alias can be used in an ORDER BY clause. 但不能用于 WHERE、GROUP BY 或 HAVING 子句中。However, it cannot be used in a WHERE, GROUP BY, or HAVING clause. 如果查询表达式是 DECLARE CURSOR 语句的一部分,则不能在 FOR UPDATE 子句中使用 column_alias 。If the query expression is part of a DECLARE CURSOR statement, column_alias cannot be used in the FOR UPDATE clause.

RemarksRemarks

选择列表中包含的 text 或 ntext 列返回的数据长度被设置为下列值中的最小值:text 列的实际大小、默认 TEXTSIZE 会话设置或硬编码应用程序限制 。The length of data returned for text or ntext columns that are included in the select list is set to the smallest value of the following: the actual size of the text column, the default TEXTSIZE session setting, or the hard-coded application limit. 若要更改会话的返回文本长度,请使用 SET 语句。To change the length of returned text for the session, use the SET statement. 默认情况下,用 SELECT 语句返回的文本数据的长度限制是 4,000 字节。By default, the limit on the length of text data returned with a SELECT statement is 4,000 bytes.

如果发生下列两种情况中的一种,SQL Server 数据库引擎SQL Server Database Engine 将引发编号为 511 的异常错误并回滚当前运行的语句:The SQL Server 数据库引擎SQL Server Database Engine raises exception 511 and rolls back the current running statement if either of the following behavior occurs:

  • SELECT 语句生成超过 8,060 字节的结果行或中间级工作表。The SELECT statement produces a result row or an intermediate work table row exceeding 8,060 bytes.

  • 尝试对超过 8,060 字节的行执行 DELETE、INSERT 或 UPDATE 语句。The DELETE, INSERT, or UPDATE statement tries an action on a row exceeding 8,060 bytes.

如果没有为 SELECT INTO 或 CREATE VIEW 语句创建的列指定列名,将会发生错误。An error occurs if no column name is specified to a column created by a SELECT INTO or CREATE VIEW statement.

另请参阅See Also

SELECT 示例 (Transact-SQL) SELECT Examples (Transact-SQL)
表达式 (Transact-SQL) Expressions (Transact-SQL)
SELECT (Transact-SQL)SELECT (Transact-SQL)