1 存储过程:create proc
1.1 基本用法
创建:
CREATE PROCEDURE [拥有者.]存储过程名[;程序编号]
@[参数名] [类型],@[参数名] [类型]
[WITH
{RECOMPILE | ENCRYPTION , ENCRYPTION}
]
[FOR REPLICATION]
AS 程序行
BEGIN
...
END;
删除:
drop procedure 存储过程名;
1.2 应用示例
【例】创建-》执行存储过程
if (exists (select * from sys.objects where name = '月报'))
drop proc 月报
go
create proc 月报(
@平台 varchar(100) ,
@日期 varchar(100) )
as
begin ---begin与end成对出现,可都不写
select * FROM [月报全览]
where 平台=@平台 and 日期>=@日期
end
;
exec 月报 'jingdong','2021-02-01'; --执行语句
2 开窗函数:over()
2.1 聚合函数vs开窗函数
- 普通的聚合函数用group by分组,每个分组返回一个统计值;
- 分析函数采用partition by分组,并且每组每行都可以返回一个统计值;
- 开窗函数指定了函数所能影响的窗口范围,也就是说在这个窗口范围中都可以受到函数的影响,有些分析函数就是开窗函数。
2.2 基本用法
function_name over(partition by 分组字段 order by 排序字段)
其中,function_name有以下几种:
- 聚合函数:sum、min、max、avg、count
- 排名函数:
① row_number( ):返回一个唯一的值,相同排名按照记录集中记录的顺序依次递增。
② rank( ):返回一个唯一的值,相同排名并列,和下一条不同记录的排名间隔断。
③ dense_rank( ):返回一个唯一的值,相同排名并列,和下一条不同记录的排名间紧邻递增。
2.3 应用示例
【例】查询最新采购单价(sql server,用于排序)
select * into 最新采购价格 from
(SELECT * from (select *,row_number() over(partition by sku,月份 order by 采购时间 desc) as rn from 最新入库清单)a
where a.rn=1)b
3 条件插入:merge into
3.1 基本用法
MERGE INTO..USING ..ON..
WHEN MATCHED THEN 【UPDATE..SET..】
WHEN NOT MATCHED THEN 【INSERT..VALUES (..)】;
【注意】merge可根据与源表联接的结果,对目标表执行插入、更新或删除操作,但merge无法多次更新同一行,也无法更新和删除同一行,故需保证数据唯一性。
3.2 应用示例
【例】综合应用:子查询-窗口函数over-联合union-条件插入merge into
MERGE INTO 国内仓销售出库成本v1 as bb
USING (
select a.*,b.需扣除总跌价 总跌价,b.需扣除总跌价*0.032*收入/sum(收入) over(partition by a.发货月份) as 总跌价分摊
from (select * from 国内仓销售出库成本v1 where 平台 in ('x','y','z') ) a
left join 本期总跌价v1_2020年 b on a.发货月份=b.本月
UNION ALL
select a.*,b.需扣除总跌价 总跌价,b.需扣除总跌价*(1-0.032)*收入/sum(收入) over(partition by a.发货月份) as 总跌价分摊
from (select * from 国内仓销售出库成本v1 where 平台 not in ('x','y','z') ) a
left join 本期总跌价v1_2020年 b on a.发货月份=b.本月
) aa
ON aa.唯一性=bb.唯一性
WHEN MATCHED THEN
update set bb.需扣除总跌价=aa.总跌价分摊;