運算式 (Transact-SQL)Expressions (Transact-SQL)

本主題適用於: 是SQL Server (從 2008 開始)是Azure SQL Database是Azure SQL 資料倉儲 是平行處理資料倉儲 THIS TOPIC APPLIES TO: yesSQL Server (starting with 2008)yesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

這是 SQL Server Database EngineSQL Server Database Engine 進行評估以取得單一資料值的符號和運算子組合。Is a combination of symbols and operators that the SQL Server Database EngineSQL 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 ]  

引數Arguments

詞彙Term 定義Definition
constantconstant 這是代表單一特定資料值的符號。Is a symbol that represents a single, specific data value. 如需詳細資訊,請參閱常數 (Transact-SQL)For more information, see Constants (Transact-SQL).
scalar_functionscalar_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.
variablevariable 這是變數或參數的名稱。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_functionranking_windowed_function 這是任何 Transact-SQLTransact-SQL 次序函數。Is any Transact-SQLTransact-SQL ranking function. 如需詳細資訊,請參閱次序函數 (Transact-SQL)For more information, see Ranking Functions (Transact-SQL).
aggregate_windowed_functionaggregate_windowed_function 這是含有 OVER 子句的任何 Transact-SQLTransact-SQL 彙總函式。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.

當利用比較或邏輯運算子來組合兩個運算式時,產生的資料類型是布林,而值是下列項目之一: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. 如需布林值資料類型的詳細資訊,請參閱比較運算子 (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.

RemarksRemarks

如果兩個運算式都有運算子所支援的資料類型,且至少符合下列條件之一,就可以用這個運算子來組合這兩個運算式: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.

另請參閱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)