sqlserver实现某字段累计求和

背景
有时候我们需要计算特定条件多条记录的某个字段的累计求和，详见下图所示

累计占比=本记录占比+上面所有行对应占比和（或者说上一行的累计占比+本行的占比）
实现
方法一：可以使用sqlserver的递归方法，不建议使用，比较复杂，且不利于带查询条件
方法二：使用over函数（下面代码视图中包含使用方法）

CREATE VIEW [dbo].[VIEW_INCHECK_UNQUALIFIED_REPORT_ALL]
    AS
    select inspection_date,material_code,material_name,specification,unqualified_num,singleRate,defect_rate,Man_Manufacture,ROUND(sum(singleRate) over ( partition by inspection_date order by unqualified_num asc ),4) total
    from (
             select a.inspection_date ,a.material_code, a.material_name,a.specification,a.unqualified_num,b.all_unqualified_num,case when b.all_unqualified_num is null then 0 else a.unqualified_num/b.all_unqualified_num end singleRate,convert(nvarchar(50), convert(decimal(4, 1), round(
                         (case when b.all_unqualified_num is null then 0 else a.unqualified_num/b.all_unqualified_num end ) * 100, 1))) + '%'    as defect_rate ,a.Man_Manufacture
             from (  select convert(nvarchar(7), a.create_time, 120)                                 as inspection_date,
                            smm.materialcode                                                          as material_code,
                            smm.materialname                                                          as material_name,
                            smm.specification                                                         as specification,
                            case when sum(convert(decimal(5, 1), a.unqualified)) =0 then null else  sum(convert(decimal(5, 1), a.unqualified)) end   as unqualified_num,
                            sup.Man_Manufacture
                     from sys_materiel_inwarehousecheck a
                              inner join sys_materiel_inwarehouselistdetail b on a.matid = b.id
                              left join mdm_supplier_manager sup on b.supplierid = sup.supplier_id
                              left join sys_materiel_materielbase smm on b.materialcode = smm.materialcode
                     group by
                         convert(nvarchar(7), a.create_time, 120),
                         smm.materialcode,
                         smm.materialname,
                         smm.specification,
                         sup.Man_Manufacture
                  ) a
                      left join
                  ( select convert(nvarchar(7), a.create_time, 120)  as inspection_date,
                           sum(convert(decimal(5, 1), a.unqualified)) as all_unqualified_num
                    from sys_materiel_inwarehousecheck a
                             inner join sys_materiel_inwarehouselistdetail b on a.matid = b.id
                             left join mdm_supplier_manager sup on b.supplierid = sup.supplier_id
                             left join sys_materiel_materielbase smm on b.materialcode = smm.materialcode
                    group by
                        convert(nvarchar(7), a.create_time, 120)
                  ) b on a.inspection_date=b.inspection_date
         ) m
go

终实现不同月份的累计相加

本文来源https://www.modb.pro/db/122248