MSSQL SERVER 2005 数学函数整理

时间:2021-11-28 00:33:07

MSSQL SERVER 2005 数学函数 
1.求绝对值 
ABS() 
select FWeight-50,ABS(FWeight-50),ABS(-5.38) from T_Person 
2.求幂 
POWER(X,Y) 用来计算X的Y次幂 
select FWeight,POWER(FWeight,-0.5),POWER(FWeight,2), 
POWER(FWeight,3),POWER(FWeight,4) from T_Person 
select Power(2,2) 
3.求平方根 
SQRT() 
select FWeight,SQRT(FWeight) from T_Person 
4. 求随机数 
RAND() 支持有参数,也可以没参数 
select RAND() 
select RAND(123) 
5.舍入到最大整数 
CEILING() 
select FName,FWeight,CEILING(FWeight),CEILING(FWeight*-1) from T_Person 
6.舍入到最小整数 
FLOOR() 
select FName,FWeight,FLOOR(FWeight),FLOOR(FWeight*-1) from T_Person 
7.四舍五入 
ROUND() 
ROUND(m,d) m为待进行四舍五入的数值,d为计算精度,也就是四舍五入时保留的小数位数 
d为0表示不保留小数位,d为负值表示在整数部分进行四舍五入。 
select FName,FWeight,ROUND(FWeight,1),ROUND(FWeight*-1,0),ROUND(FWeight,-1) from T_Person 
8.求正弦值 
SIN() 
select FName,FWeight,SIN(FWeight)from T_Person 
9.求余弦值 
COS() 
select FName,FWeight,COS(FWeight) from T_Person 
10.求反正弦 
ASIN() 
select FName,FWeight,ASIN(1/FWeight) from T_Person 
11.求反余弦 
ACOS() 
select FName,FWeight,ACOS(1/FWeight) from T_Person 
12.求正切值 
TAN() 
select FName,FWeight,TAN(FWeight) from T_Person 
13.求反正切值 
ATAN() 
select FName,FWeight,ATAN(FWeight) from T_Person 
14.求两个变量的反正切 
ATN2(X,Y) 类似于计算Y/X的反正切 
select FName,FWeight,ATN2(Fweight,2) from T_Person 
15.求余切 
COT() 
select FName,FWeight,COT(Fweight) from T_Person 
16.求圆周率π值 
PI() 
select FName,FWeight,Fweight*PI(),PI() from T_Person 
17.弧度制转换为角度制 
DEGREES() 结果的精确度与参数有关 
select DEGREES(PI()),DEGREES(3.0),DEGREES(3) 
18.角度制转换为弧度制 
RADIANS() 结果的精确度与参数有关 
select RADIANS(180),RADIANS(180.0) 
19.求符号 
SIGN() 返回一个数值的符号,如果数值大于0则返回1, 
如果数值等于0则返回0,如果数值小于0则返回-1. 
结果的精确度与参数有关 
select FName,FWeight-48.68,SIGN(FWeight-48.68),SIGN(1),SIGN(1.000) from T_Person 
20.求整除余数 % 
select FName,FWeight,FWeight%5 from T_Person 
21.求自然对数 
LOG() 
select FName,FWeight,LOG(FWeight),LOG(1.00) from T_Person 
22.求以10为底的对数 
LOG10() 
select FName,Fweight,LOG10(FWeight),LOG10(100) from T_Person 


ps: 

主要参照《程序员的SQL金典》 
实例有所改动。 
T_Person表的创建 

复制代码代码如下:


create table T_Person 

FIdNumber varchar(20), 
FName varchar(20), 
FBirthDay datetime, 
FRegDay datetime, 
FWeight numeric(10,2),