POWER (Transact-SQL)POWER (Transact-SQL)

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

傳回指定乘冪之指定運算式的值。Returns the value of the specified expression to the specified power.

主題連結圖示 Transact-SQL 語法慣例Topic link icon Transact-SQL Syntax Conventions

語法Syntax

POWER ( float_expression , y )  

引數Arguments

float_expressionfloat_expression
float 類型或能夠隱含轉換成 float 類型的運算式Is an expression of type float or of a type that can be implicitly converted to float.

yy
float_expression 相乘的乘冪。Is the power to which to raise float_expression. y 可為精確數值或近似數值資料類型類別目錄的運算式,但 bit 資料類型除外。y can be an expression of the exact numeric or approximate numeric data type category, except for the bit data type.

傳回類型Return Types

傳回型別取決於 float_expression 的輸入類型:The return type depends on the input type of float_expression:

輸入類型Input type 傳回類型Return type
floatrealfloat, real floatfloat
decimal(p, s)decimal(p, s) decimal(38, s)decimal(38, s)
intsmallinttinyintint, smallint, tinyint intint
bigintbigint bigintbigint
moneysmallmoneymoney, smallmoney moneymoney
bitcharncharvarcharnvarcharbit, char, nchar, varchar, nvarchar floatfloat

如果結果不符合傳回型別,就會發生算術溢位錯誤。If the result does not fit in the return type, an arithmetic overflow error occurs.

範例Examples

A.A. 使用 POWER 傳回數字的立方Using POWER to return the cube of a number

下列範例示範將某個數字自乘 3 次 (數字的立方)。The following example demonstrates raising a number to the power of 3 (the cube of the number).

DECLARE @input1 float;  
DECLARE @input2 float;  
SET @input1= 2;  
SET @input2 = 2.5;  
SELECT POWER(@input1, 3) AS Result1, POWER(@input2, 3) AS Result2;  

以下為結果集:Here is the result set.

Result1                Result2  
---------------------- ----------------------  
8                      15.625  
  
(1 row(s) affected)  

B.B. 利用 POWER 來顯示資料類型轉換的結果Using POWER to show results of data type conversion

下列範例示範 float_expression 如何保留可能會傳回非預期結果的資料類型。The following example shows how the float_expression preserves the data type which can return unexpected results.

SELECT   
POWER(CAST(2.0 AS float), -100.0) AS FloatResult,  
POWER(2, -100.0) AS IntegerResult,  
POWER(CAST(2.0 AS int), -100.0) AS IntegerResult,  
POWER(2.0, -100.0) AS Decimal1Result,  
POWER(2.00, -100.0) AS Decimal2Result,  
POWER(CAST(2.0 AS decimal(5,2)), -100.0) AS Decimal2Result;  
GO  

以下為結果集:Here is the result set.

FloatResult            IntegerResult IntegerResult Decimal1Result Decimal2Result Decimal2Result  
---------------------- ------------- ------------- -------------- -------------- --------------  
7.88860905221012E-31   0             0             0.0            0.00           0.00  

C.C. 使用 POWERUsing POWER

下列範例會傳回 POWER2 結果。The following example returns POWER results for 2.

DECLARE @value int, @counter int;  
SET @value = 2;  
SET @counter = 1;  
  
WHILE @counter < 5  
   BEGIN  
      SELECT POWER(@value, @counter)  
      SET NOCOUNT ON  
      SET @counter = @counter + 1  
      SET NOCOUNT OFF  
   END;  
GO  

以下為結果集:Here is the result set.

-----------   
2             
  
(1 row(s) affected)  
  
-----------   
4             
  
(1 row(s) affected)  
  
-----------   
8             
  
(1 row(s) affected)  
  
-----------   
16            
  
(1 row(s) affected)  

範例:Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW)平行處理資料倉儲Parallel Data WarehouseExamples: Azure Synapse Analytics (SQL DW)Azure Synapse Analytics (SQL DW) and 平行處理資料倉儲Parallel Data Warehouse

D.使用 POWER 傳回數字的立方D: Using POWER to return the cube of a number

下列範例示範傳回 2.0 的 3 次方 POWER 結果。The following example shows returns POWER results for 2.0 to the 3rd power.

SELECT POWER(2.0, 3);  

以下為結果集:Here is the result set.

------------ 
8.0

另請參閱See Also

decimal 和 numeric (Transact-SQL) decimal and numeric (Transact-SQL)
float 和 real (Transact-SQL) float and real (Transact-SQL)
int、bigint、smallint 和 tinyint (Transact-SQL) int, bigint, smallint, and tinyint (Transact-SQL)
數學函式 (Transact-SQL) Mathematical Functions (Transact-SQL)
money 和 smallmoney (Transact-SQL)money and smallmoney (Transact-SQL)