表达式(Transact-SQL)Expressions (Transact-SQL)

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 是Azure SQL 数据库Azure SQL DatabaseYesAzure SQL 数据库Azure SQL Database 是Azure SQL 托管实例Azure SQL Managed InstanceYesAzure SQL 托管实例Azure SQL Managed Instance 是Azure Synapse AnalyticsAzure Synapse AnalyticsyesAzure Synapse AnalyticsAzure Synapse Analytics 是并行数据仓库Parallel Data Warehouseyes并行数据仓库Parallel Data Warehouse

符号和运算符的一种组合,SQL Server 数据库引擎SQL Server Database Engine将处理该组合以获得单个数据值。Is a combination of symbols and operators that the SQL Server 数据库引擎SQL Server Database Engine evaluates to obtain a single data value. 简单表达式可以是一个常量、变量、列或标量函数。Simple expressions can be a single constant, variable, column, or scalar function. 可以用运算符将两个或更多的简单表达式联接起来组成复杂表达式。Operators can be used to join two or more simple expressions into a complex expression.

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

语法Syntax

-- Syntax for SQL Server and Azure SQL Database  
  
{ constant | scalar_function | [ table_name. ] column | variable   
    | ( expression ) | ( scalar_subquery )   
    | { unary_operator } expression   
    | expression { binary_operator } expression   
    | ranking_windowed_function | aggregate_windowed_function  
}  
-- Syntax for Azure SQL Data Warehouse and Parallel Data Warehouse  

-- Expression in a SELECT statement  
<expression> ::=   
{  
    constant   
    | scalar_function   
    | column  
    | variable  
    | ( expression  )  
    | { unary_operator } expression   
    | expression { binary_operator } expression   
}  
[ COLLATE Windows_collation_name ]  
  
-- Scalar Expression in a DECLARE, SET, IF...ELSE, or WHILE statement  
<scalar_expression> ::=  
{  
    constant   
    | scalar_function   
    | variable  
    | ( expression  )  
    | (scalar_subquery )  
    | { unary_operator } expression   
    | expression { binary_operator } expression   
}  
[ COLLATE { Windows_collation_name ]  
  

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法,请参阅早期版本文档To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

参数Arguments

术语Term 定义Definition
constant constant 表示单个特定数据值的符号。Is a symbol that represents a single, specific data value. 有关详细信息,请参阅常量 (Transact-SQL)For more information, see Constants (Transact-SQL).
scalar_function scalar_function 一个提供特定服务并返回单个值的 Transact-SQLTransact-SQL 语法单元。Is a unit of Transact-SQLTransact-SQL syntax that provides a specific service and returns a single value. scalar_function 可以是内置标量函数(如 SUM、GETDATE 或 CAST 函数),也可以是标量用户定义函数 。scalar_function can be built-in scalar functions, such as the SUM, GETDATE, or CAST functions, or scalar user-defined functions.
[ table_name. [ table_name. ]] 表的名称或别名。Is the name or alias of a table.
columncolumn 列的名称。Is the name of a column. 表达式中只允许列的名称。Only the name of the column is allowed in an expression.
variable variable 变量或参数的名称。Is the name of a variable, or parameter. 有关详细信息,请参阅 DECLARE @local_variable (Transact-SQL)For more information, see DECLARE @local_variable (Transact-SQL).
( expression )( expression ) 本主题中定义的任意一个有效表达式。Is any valid expression as defined in this topic. 括号是分组运算符,用于确保先运算括号内表达式中的运算符,然后再将结果与别的表达式组合。The parentheses are grouping operators that make sure that all the operators in the expression within the parentheses are evaluated before the resulting expression is combined with another.
( scalar_subquery )( scalar_subquery ) 返回一个值的子查询。Is a subquery that returns one value. 例如:For example:

SELECT MAX(UnitPrice)

FROM Products
{ unary_operator } { unary_operator } 一元运算符只能用于计算结果数据类型属于数字数据类型类别的表达式。Unary operators can be applied only to expressions that evaluate to any one of the data types of the numeric data type category. 只有一个数字操作数的运算符:Is an operator that has only one numeric operand:

+ 指示正数。+ indicates a positive number.

- 指示负数。- indicates a negative number.

~ 指示一的补数运算符。~ indicates the one's complement operator.
{ binary_operator } { binary_operator } 用于定义如何组合两个表达式以得到一个结果的运算符。Is an operator that defines the way two expressions are combined to yield a single result. binary_operator 可以是算术运算符、赋值运算符 (=)、位运算符、比较运算符、逻辑运算符、字符串连接运算符 (+) 或一元运算符 。binary_operator can be an arithmetic operator, the assignment operator (=), a bitwise operator, a comparison operator, a logical operator, the string concatenation operator (+), or a unary operator. 有关运算符的详细信息,请参阅运算符 (Transact-SQL)For more information about operators, see Operators (Transact-SQL).
ranking_windowed_function ranking_windowed_function 任意 Transact-SQLTransact-SQL 排名函数。Is any Transact-SQLTransact-SQL ranking function. 有关详细信息,请参阅排名函数 (Transact-SQL)For more information, see Ranking Functions (Transact-SQL).
aggregate_windowed_function aggregate_windowed_function 任意包含 Transact-SQLTransact-SQL OVER 子句的聚合函数。Is any Transact-SQLTransact-SQL aggregate function with the OVER clause. 有关详细信息,请参阅 OVER 子句 (Transact-SQL)For more information, see OVER Clause (Transact-SQL).

表达式结果Expression Results

对于由单个常量、变量、标量函数或列名组成的简单表达式,其数据类型、排序规则、精度、小数位数和值就是它所引用的元素的数据类型、排序规则、精度、小数位数和值。For a simple expression made up of a single constant, variable, scalar function, or column name: the data type, collation, precision, scale, and value of the expression is the data type, collation, precision, scale, and value of the referenced element.

用比较运算符或逻辑运算符组合两个表达式时,生成的数据类型为 Boolean,并且值为下列类型之一:TRUE、FALSE 或 UNKNOWN。When two expressions are combined by using comparison or logical operators, the resulting data type is Boolean and the value is one of the following: TRUE, FALSE, or UNKNOWN. 有关 Boolean 数据类型的详细信息,请参阅比较运算符 (Transact SQL)For more information about Boolean data types, see Comparison Operators (Transact-SQL).

用算术运算符、位运算符或字符串运算符组合两个表达式时,生成的数据类型取决于运算符。When two expressions are combined by using arithmetic, bitwise, or string operators, the operator determines the resulting data type.

由多个符号和运算符组成的复杂表达式的计算结果为单值结果。Complex expressions made up of many symbols and operators evaluate to a single-valued result. 生成的表达式的数据类型、排序规则、精度和值由进行组合的两个表达式决定,并按每次两个表达式的顺序递延,直到得出最后结果。The data type, collation, precision, and value of the resulting expression is determined by combining the component expressions, two at a time, until a final result is reached. 表达式中元素组合的顺序由表达式中运算符的优先级决定。The sequence in which the expressions are combined is defined by the precedence of the operators in the expression.

备注Remarks

两个表达式可以由一个运算符组合起来,只要它们具有该运算符支持的数据类型,并且满足至少下列一个条件:Two expressions can be combined by an operator if they both have data types supported by the operator and at least one of these conditions is true:

  • 两个表达式有相同的数据类型。The expressions have the same data type.

  • 优先级低的数据类型可以隐式转换为优先级高的数据类型。The data type with the lower precedence can be implicitly converted to the data type with the higher data type precedence.

如果表达式不满足这些条件,则可以使用 CAST 或 CONVERT 函数将优先级低的数据类型显式转化为优先级高的数据类型,或者转换为一种可以隐式转化成优先级高的数据类型的中间数据类型。If the expressions do not meet these conditions, the CAST or CONVERT functions can be used to explicitly convert the data type with the lower precedence to either the data type with the higher precedence or to an intermediate data type that can be implicitly converted to the data type with the higher precedence.

如果没有支持的隐式或显式转换,则两个表达式将无法组合。If there is no supported implicit or explicit conversion, the two expressions cannot be combined.

任何计算结果为字符串的表达式的排序规则都应遵循排序规则优先顺序规则。The collation of any expression that evaluates to a character string is set by following the rules of collation precedence. 有关详细信息,请参阅排序规则优先顺序 (Transact-SQL)For more information, see Collation Precedence (Transact-SQL).

在 C 或 MicrosoftMicrosoft Visual BasicVisual Basic 这类编程语言中,表达式的计算结果始终为单值结果。In a programming language such as C or MicrosoftMicrosoft Visual BasicVisual Basic, an expression always evaluates to a single result. Transact-SQLTransact-SQL 选择列表中的表达式按以下规则进行变体:分别对结果集中的每一行计算表达式的值。Expressions in a Transact-SQLTransact-SQL select list follow a variation on this rule: The expression is evaluated individually for each row in the result set. 同一个表达式对结果集内的每一行可能会有不同的值,但该表达式在每一行的值是唯一的。A single expression may have a different value in each row of the result set, but each row has only one value for the expression. 例如,在 SELECT 语句中,对 ProductID 的引用以及选择列表中的术语 1+2 都是表达式:For example, in the following SELECT statement both the reference to ProductID and the term 1+2 in the select list are expressions:

USE AdventureWorks2012;  
GO  
SELECT ProductID, 1+2  
FROM Production.Product;  
GO  

结果集中的每个行的表达式 1+2 的计算结果都为 3The expression 1+2 evaluates to 3 in each row in the result set. 虽然表达式 ProductID 在结果集的每一行中产生一个唯一值,但每一行只有一个 ProductID 值。Although the expression ProductID generates a unique value in each result set row, each row only has one value for ProductID.

  • Azure SQL 数据仓库为每个线程分配固定的最大内存量,因此,任何线程都无法占用所有内存。Azure SQL Data Warehouse allocates a fixed maximum amount of memory to each thread so no thread can use up all the memory. 其中一些内存用于存储查询的表达式。Some of this memory is used for storing queries’ expressions. 如果查询包含太多表达式,并且其所需内存超出内部限制,则引擎不会执行该查询。If a query has too many expressions and its required memory exceeds the internal limit, the engine will not execute it. 若要避免发生此问题,用户可以将查询更改为多个查询,使每个查询包含较少的表达式。To avoid this problem, users can change the query into multiple queries with smaller number of expressions in each. 例如,在 WHERE 子句中有一个具有许多表达式的查询:For example, you have a query with a long list of expressions in the WHERE clause:
DELETE FROM dbo.MyTable 
WHERE
(c1 = '0000001' AND c2 = 'A000001') or
(c1 = '0000002' AND c2 = 'A000002') or
(c1 = '0000003' AND c2 = 'A000003') or
...

将此查询更改为:Change this query to:

DELETE FROM dbo.MyTable WHERE (c1 = '0000001' AND c2 = 'A000001');
DELETE FROM dbo.MyTable WHERE (c1 = '0000002' AND c2 = 'A000002');
DELETE FROM dbo.MyTable WHERE (c1 = '0000003' AND c2 = 'A000003');
...

另请参阅See Also

AT TIME ZONE (Transact-SQL) AT TIME ZONE (Transact-SQL)
CASE (Transact-SQL) CASE (Transact-SQL)
CAST 和 CONVERT (Transact-SQL) CAST and CONVERT (Transact-SQL)
COALESCE (Transact-SQL) COALESCE (Transact-SQL)
数据类型转换(数据库引擎) Data Type Conversion (Database Engine)
数据类型优先级 (Transact-SQL) Data Type Precedence (Transact-SQL)
数据类型 (Transact-SQL) Data Types (Transact-SQL)
内置函数 (Transact-SQL) Built-in Functions (Transact-SQL)
LIKE (Transact-SQL) LIKE (Transact-SQL)
NULLIF (Transact-SQL) NULLIF (Transact-SQL)
SELECT (Transact-SQL) SELECT (Transact-SQL)
WHERE (Transact-SQL)WHERE (Transact-SQL)