【SQL Server学习笔记】SQL Server系统函数

 

SQL Server的系统函数，可以让我们实现聚合、数学运算、字符串操作、日期运算、NULL的处理、排序等功能。

需要注意的是SQL Server的系统函数和用户自定义函数是不同的，系统函数是SQL Server内部支持的函数，不需要我们先定义，再使用。

我们通过Microsoft SQL Server Management Studio客户端工具，可以看到SQL Server提供的系统函数，如下图：

 

下面，通过实例，来说明如何使用SQL Server的内建函数：

 

1、聚合函数

--基本聚合函数
SELECT MAX(wcId),
       MIN(WCID),
       COUNT(WCiD),
       COUNT_BIG(wcId),
       COUNT(*),
       SUM(WCiD),
       AVG(WCID)            
FROM dbo.wct


use AdventureWorks
go

/*====================================================
x1,x2,...,xn共n个数，m是这n个数的平均数

   var：square(x1-m)+square(x2-m)+...+square(xn-m)。
  varp：var/n

 stdev:sqrt(var)
stdevp:sqrt(varp)        
======================================================*/
select VAR(taxAmt),
       VARP(taxAmt),
       
       STDEV(taxAmt),
       STDEVP(taxAmt),
       
       case when STDEV(taxAmt) = sqrt(VAR(taxAmt))
                 then 'equal'
            else 'not equal'
       end,  
            
       case when STDEVP(taxAmt) = sqrt(VARP(taxAmt))
                 then 'equal'
            else 'not equal'
       end,
       
       checksum_agg(SalesOrderID)  --返回组中各值的校验和      
from sales.SalesOrderHeader

2、数学函数

SELECT ABS(-1),      --绝对值
       CEILING(1.56),--大于指定值的最小整数
       FLOOR(1.56),  --小于指定值的最大整数
       RAND(10),     --返回0~1之间的浮点数，如果要每次返回相同的数,可以指定seed
                     --如未指定seed，则SQL Server数据库引擎随机分配种子值
       ROUND(28.9,0,0),--第三个参数指定0时表示四舍五入,1表示截断（不会进位）
       ROUND(28.9,0,1), 
       

       LOG(2),                 --指定参数的对数
       EXP(0.693147180559945), --指定参数的指数
       LOG10(2),               --指定参数的自然对数
       POWER(2,10),            --第一个参数对第二个参数的幂
       SQUARE(2),              --平方
       SQRT(2),                --开方
       

       PI(),               --PI常量3.1415926
       DEGREES(PI()),      --把弧度转为角
       RADIANS(180.0),     --把角度转为弧度,返回值的类型与输入参数类型相同
       
       SIN(1),                 --指定角或弧度的正弦值
       ASIN(0.841470984807897),--由参数所指定的正弦值,来计算其所对应的角或弧度
              
       COS(1),                 --指定角或弧度的余弦值
       ACOS(0.54030230586814), --由参数所指定的余弦值,来计算其所对应的角或弧度
              
       TAN(1),                 --指定角或弧度的正切值
       ATAN(1.5574077246549),  --由参数所指定的正切值,来计算其所对应的角或弧度  
       
       --指定角或弧度的余切值          
       COT(1),   

       --返回以弧度表示的角，该角位于正 X 轴和原点至点 (y, x) 的射线之间，
       --其中 x 和 y 是两个指定的浮点表达式的值。
       ATN2(1,2)

3、处理NULL的函数

DECLARE @V1 INT,@V2 INT,@v3 numeric(10,0)

SELECT @V1 = 10,
       @V2 = NULL,
       @v3 = 0

SELECT ISNULL(@v1,0),         --如果参数一为null,那么返回参数二,否则返回参数一
       ISNULL(@v2,0),
       
       coalesce(@V1,@v2,@v3), --返回第一个非NULL值
       coalesce(@v2,@v3,@v1),
       
       nullif(@V3,100),    --如果参数一等于参数二，那么返回NULL,否则还是返回参数一
       100 / nullif(@v3,0) --用来解决除数为0的问题 
 

4、排名函数 

select * into objectT
from sys.objects

insert into objectT
select top (10) *
from sys.objects

--生成递增行号
select object_id,
       name,
       ROW_NUMBER() over(order by object_id)
from objectT


--排名,会有间隔,比如排第1的有2行数据,那么接下来直接就是排在第3的
select OBJECT_ID,
       name,
       RANK() over(order by object_id)
from objectT


--无间隔排名
select OBJECT_ID,
       name,
       dense_rank() over(order by object_id)
from objectT


/*===============================================
根据排序,可选的分区，把结果集分成指定数量的分组
ntile(n) partition by x order by y:

按照x分组，然后按y排序，然后把每个分组分成n个分组
=================================================*/
select OBJECT_ID,
       name,
       type_desc,
       NTILE(3) over(partition by type_desc
                         order by object_id)
from objectT