一、四舍五入
1、 Round(@num,2) 保留两位有效数字,四舍五入但不去掉零
2、Convert(decimal(18,2),@num)
3、cast(@num as decimal(18,2))
二、开窗函数(相比聚合分组可以返回多组值)
格式:函数名(列多)Over(partitionby列名_order by列名)
1、排序函数
有相等的值的情况排序:
row_number() Over()函数只会显示一个(不会有重复的排序数值),并按顺序排序
rank() Over() 会全部显示(有重复值排序数值),但跳跃排序
dense_rank() 会全部显示出来(有重复值排序数值),但依然按顺序排序
2、取偏移量
lag():
lag(field, num, defaultvalue)
field: 需要查找的字段
num: 往签查找的num行的数据
defaultvalue: 没有符合条件的默认值,可以忽略
lead():
lead(field, num, defaultvalue)
field: 需要查找的字段
num: 往后查找的num行的数据
defaultvalue: 没有符合条件的默认值,可以忽略
三、聚合函数
sum()、avg() 函数括号里面可以写条件,比如iif等
四、连接函数
CONCAT_WS ( separator, argument1, argument2 [, argumentN]... )
五、分页(参考文档:https://www.jianshu.com/p/d1ae74bda1c5)
Top Not IN、offset 2 row fetch next 5 row only
SELECT* FROM (
SELECT*,ROW_NUMBER() OVER (ORDER BY Id) AS RowNumber FROM Users ) as b
where RowNumber BETWEEN 0 and 3
取总页数
ROW_NUMBER() OVER (ORDER BY aa DESC,bb ASC,cc DESC) Rank,COUNT(1) OVER() AS Total(参考文档:【工作笔记0034】Sqlserver开窗函数Over()实现翻页并带出总条数_adamlevine7的博客-CSDN博客_sqlserver开窗函数)
六、表转向
//竖向转横向
select
id
, sum(case 'month'when 'Jan' then revenue else null end) as Jan_Revenue
, sum(case 'month'when 'Feb' then revenue else null end) as Feb_Revenue
, sum(case 'month'when 'Mar' then revenue else null end) as Mar_Revenue
, sum(case 'month'when 'Apr' then revenue else null end) as Apr_Revenue
, sum(case 'month'when 'May' then revenue else null end) as May_Revenue
, sum(case 'month'when 'Jun' then revenue else null end) as Jun_Revenue
, sum(case 'month'when 'Jul' then revenue else null end) as Jul_Revenue
, sum(case 'month'when 'Aug' then revenue else null end) as Aug_Revenue
, sum(case 'month'when 'Sep' then revenue else null end) as Sep_Revenue
, sum(case 'month'when 'Oct' then revenue else null end) as Oct_Revenue
, sum(case 'month'when 'Nov' then revenue else null end) as Nov_Revenue
, sum(case 'month'when 'Dec' then revenue else null end) as Dec_Revenue
from Department group by id
//横向转竖向
select product_id,'store1' store, store1 price from Products where store1 is not null
union all
select product_id,'store2', store2 from Products where store2 is not null
union all
select product_id,'store3', store3 from Products where store3 is not null