[SQL SERVER系列]之常用函数和开窗函数介绍及实例

本文主要介绍SQL SERVER数据库中一些常用的系统函数及其SQL SERVER 2005以上支持的开窗函数。

1.常用函数

--从字符串右边截取指定字符数

select RIGHT('HELLO', 2)   --LO

 

--受影响的行数

select @@ROWCOUNT

 

--求绝对值ABS()

SELECT ABS(-2.0)   --2.0

 

--计算指数的函数POWER()

SELECT POWER(4, 0.5)

SELECT POWER(4, 3)

 

--求平方根SQRT()

SELECT SQRT(64.02)

 

--取随机数，返回到之间的随机浮点值Rand()

SELECT Rand()

 

--将字符串转换为小写LOWER()

SELECT LOWER('HELLO WORLD')

 

--将字符串转换为大写UPPER()

SELECT UPPER(LOWER('HELLO WORLD'))

 

--取ASCII码ASCII()

SELECT ASCII('A')   --65

 

--取对应ASCII码的字符CHAR()

SELECT CHAR(ASCII('A'))   --A

 

--日期时间函数

--获取当前时间

SELECT GETDATE()

 

--获取日期部分

SELECT CONVERT(VARCHAR(64), GETDATE(), 101)  --03/11/2014

 

--获取时间部分

SELECT CONVERT(VARCHAR(64), GETDATE(), 108)  --17:00:10

 

--获取某个日期是礼拜几

SELECT DATENAME(WEEKDAY, GETDATE())

 

--返回guid，位的唯一值

select Newid()

 

--若P1非空，返回P1；否则，返回default默认值

COALESCE(P1，default)

COALESCE(P.FName, '没有开单人')

 

--返回当前所用语言名称

select @@language

 

--返回允许同时进行的最大用户连接数

select @@MAX_CONNECTIONS

 

--返回本地sql server的名称

select @@SERVERNAME

 

--返回当前安装的sql server的日期，版本和处理器类型

print @@VERSION

 

--确定输入的表达式是否为有效的日期

select ISDATE('2014-03-17')

 

select convert(varchar(32), 123.00)+'abc'  --123.00abc

select CAST(123.00 as varchar(32))+'abc'  --123.00abc

 

select cast('abc' as int)  --转换出错

select @@ERROR   --结果为，返回执行上一个sql语句的错误号，若没有错误，结果为

 

2.开窗函数

可以看到与聚合函数不同的是，开窗函数在聚合函数后增加了一个 OVER 关键字。开窗函数的调用格式为： 函数名(列) OVER(选项)   OVER 关键字表示把函数当成开窗函数而不是聚合函数。SQL 标准允许将所有聚合函数用做开窗函数，使用 OVER 关键字来区分这两种用法。 

开窗函数 COUNT(*) OVER()对于查询结果的每一行都返回所有符合条件的行的条数。OVER 关键字后的括号中还经常添加选项用以改变进行聚合运算的窗口范围。如果 OVER 关键字后的括号中的选项为空，则开窗函数会对结果集中的所有行进行聚合运算。 

SELECT FCITY , FAGE , COUNT(*) OVER() FROM T_Person WHERE FSALARY<5000   执行完毕我们就能在输出结果中看到下面的执行结果：COUNT(*) OVER()返回工资小于5000的所有记录数。

 

--ROW_NUMBER() OVER返回按某种规则排序后的某行的行数，只有SQL SERVER 2005以上支持

select * from (select ROW_NUMBER() OVER(ORDER BY UserID DESC) as RowNum, * FROM Tse_User) as A

 WHERE A.RowNum >= 3 AND A.RowNum <= 4

 

-COUNT(0) OVER(), 统计总数

select *, COUNT(0) OVER() from dbo.T_Reader where fage > 30

 

--COUNT(0) OVER(PARTITION BY fyearofbirth), 统计某个分区的数量

select *,COUNT(0) OVER(PARTITION BY fage) FROM T_Reader 

 

--Rank()遇相同的编号顺延，DENSE_RANK()遇相同的编号不变

select *, RANK() over(order by fage),

DENSE_RANK() over(order by fage),

ROW_NUMBER() over(order by fage) from t_reader

 

--按年龄排序，分成份，并计算每行对应的编号

select *, NTILE(4) over(order by fsalary) from t_reader

 

--通过分区函数实现计算某个产品的销售量在同类产品总销售量中所占百分比

select T_Merchandise.FName, T_SaleBillDetail.FCount, 

T_SaleBillDetail.FCount*1.0/SUM(T_SaleBillDetail.FCount) over(partition by T_Merchandise.FName) from 

T_SaleBillDetail inner join T_Merchandise

on T_SaleBillDetail.FMerchandiseId = T_Merchandise.FId

 

 

如果您有什么问题，欢迎在下面评论，我们一起讨论，谢谢~

如果您觉得还不错，不妨点下右下方的推荐，有您的鼓励我会继续努力的~