SqlServer常用内置函数

--=======================================
-- SQL常用内置函数
--=======================================

--判断为null函数
declare @abc varchar(5)
set @abc='mnw'
select isnull(@abc,0);  --如果变量abc值为null，则取0，否则取被赋的值

--查找索引函数
select charindex('abc','bac abc fmd abc'); --获取字符串abc在长字符串bac abc fmd abc中的索引位置，索引默认从1开始找
select charindex('abc','bac abc fmd abc',9); --索引从9开始找

--删除指定长度的字符，并在指定的起点处插入另一组字符
SELECT STUFF('abcdef', 2, 3, 'ijklmn')
aijklmnef

--重复获取函数
select replicate('m',3); --按指定次数重复字符表达式，将m字符重复三次
select replicate('abc',2);--将字符串abc重复2次

--反转

REVERSE(character_expression )

--大小写转换函数
select len('datazsrt'); --获取字符串长度
select lower('ADRTddd'); --转小写
select upper('data');--转大写

--SQL GUID去除横线,并转换为小写
 LOWER(REPLACE(LTRIM(NEWID()),'-',''))

--替换截取函数
select substring('abcdef111',2,3); --截取字符串

select replace('dadt123','t1','AA'); --替换字符串

--类型转换函数
select cast('2015-08-02' as datetime) as 日期类型; --数据类型转换，CAST是ANSI兼容的，而CONVERT则不是

select 'ab'+cast(1 as varchar);-- 将1转化为字符串与ab相加

select convert(datetime,'2015-08-02'); --数据类型转换，与cast相同

select convert(varchar(19),getdate(),113); --不同的是convert还提供一些特别的日期格式转换，而cast没有这个功能

select format(getdate(),'yyyy/mm/dd'); --将日期转化为规定的格式

   
   
--数学函数    
select round(2.15,1); --四舍五入,第二个参数索引从0开始，即：0表示小数后第一位进行判断

select ceiling(2.158); --向上取整

select floor(2.713); --向下取整

除法运算

select CAST( 9*1.0/4 as decimal(18,2) ) sad from table_1; 运行后结果：2.25

等同于

select Convert(decimal(18,2),9*1.0/4)  sad from table_1; 运行后结果：2.25

--日期函数
select getdate();

select datepart(mm,'2015-08-04 15:28:26');  --返回日期的某一部分  yy,mm,dd,hh,mi,ss,

select dateadd(dd,-25,'2015-08-04 15:28:26'); --在日期中加上或减去制定的时间间隔,给天数减25天

select datediff(day,'2008-06-05','2008-08-05') as 天数; --返回两个日期之间的天数

-- 当月第一天
select dateadd(month, datediff(month, 0, getdate()), 0)
-- 当月最后一天(思路：下月的第一天减去一天)
select dateadd(month, datediff(month, 0, dateadd(month, 1, getdate())), -1)
-- 当年第一天
select dateadd(year, datediff(year, 0, getdate()), 0)
-- 当年最后一天(思路：下年的第一天减去一天)
select dateadd(year, datediff(year, 0, dateadd(year, 1, getdate())), -1)
-- 当天零时2018-10-15 00:00:00.000
select dateadd(day, datediff(day, 0, getdate()), 0)

--聚合函数
count(),min(),max(),avg(),sum()

--4个排名函数,都可以与over()结合使用
row_number(),rank(),dense_rank(),ntile()

select row_number() over(order by column_name),* from table_name; --按某个字段排名然后查询出所有值，它会为查询出来的每一行记录生成一个序号，依次排序且不会重复
select row_number() over(order by column_name),* from table_name order by column_name desc; --先按某个字段排名给出序号，然后再根据另一个字段进行倒叙排列，排出序号可能会被打乱

select rank() over(order by column_name),* from table_name; --rank函数会考虑到column_name中的值会重复或相同，那个给出的序号也会相同。但生成的序号不是连续的，rank是跳跃排序

select dense_rank() over(order by column_name),* from table_name; --dense_rank函数不同与rank函数，它生成的序号是连续的，dense_rank不会跳跃

--ntile()函数还是比较麻烦的排序分组函数

select ntile(4) over(order by column_name des),* from table_name;  --先对column_name进行倒序排列， 参数4表示分为4组，每组中有多少个相同的序号要根据（总记录数/总组数）+1 条

--如果一个表中有6条数据，那么(6/4)+1=2;每组中有两个相同的记录数，末尾的根据情况分配，必须要分4组。但如果是8条数据，能整除，8/4=2;就不需要再加1，每组中平均分两条相同记录数的数据

IIf 
返回由逻辑测试确定的两个数值或字符串值之一。

语法 
数字 
IIf(Logical EXPression, Numeric Expression1, Numeric Expression2)

如果 Logical Expression 取值为 TRUE，则此函数返回 Numeric Expression1，否则，返回 Numeric Expression2。

字符串 
IIf(Logical Expression, String Expression1, String Expression2)

如果 Logical Expression 取值为 TRUE，则此函数返回 String Expression1，否则，返回 String Expression2。

注释 
只有当 Logical Expression 的值为零时，才认为该表达式是 FALSE。任何其它值都被解释为 TRUE。 
不推荐用 Iif 函数基于搜索条件创建成员的集合。请改用 Filter 函数根据逻辑表达式评估指定集合中的每个成员，然后返回成员的子集合。

示例
数字
如果 Measures.CurrentMember 是空单元，则下面的示例返回 0，否则返回 1： 
IIf(IsEmpty(Measures.CurrentMember), 0, 1)

字符串 
如果 Measures.CurrentMember 是空单元，则下面的字符串返回字符串 "Yes"，否则返回字符串 "No"： 
IIf(IsEmpty(Measures.CurrentMember), "Yes", "No")

在Access中我可以用IIF函数进行统计汇总，比如，要知道实际应该交费的用户个数:
Select sum(iif(金额>0, 1,0)) as num from 费用
在SQL Server中好像没有对应的函数，我用：
select sum(case when 金额>0 then 1 else 0 end) as num from 费用
好像不太直观，不知道有没有其它方法