2023年7月13日发(作者：)

【SQLServer】统计指定年份的1-12⽉份每个⽉的数据如图所⽰，现在有⼀个需求，主要是按当前年份查询仓库每⼀个⽉的库存流⽔和配件的库存占⽐情况 其要求如下：控件查询条件服务站类型

长度

备注

服务站的查询条件来源：可⽤的服务站档案必选，可以多选配件名称 配件名称的查询条件数据来源：可⽤的产品物料数据必选，可以多选年度显⽰信息标题Picklist

Expression

前五年后⼗年的范围，单选查询结果表达式，显⽰“【参数年度所选值】年度客户回款报表“服务站 显⽰服务站名称，此列为⾏分组。数据来源：出库单实体发货仓库配件名称

数据来源：出库单实体显⽰配件名称值，此列为⾏分组。字体显⽰蓝⾊，下划线效果数据来源：出库单明细实体数量⽉份显⽰结果

数据来源：出库单明细实体数据来源：出库单实体创建时间排序：⼀级⾏分组按服务站名称排序，⼆级按配件名称正序排序显⽰列：服务站，发货仓库，配件名称，数量，每⽉总数。显⽰规则：所有数字显⽰精确到两位⼩数。

总计 针对本报表的汇总统计：配件⼀年内申请数量总和。语句如下：829363738394647484956575859DECLARE @userid uniqueidentifier = '9FC299F4-6E20-EA11-A12B-000C29D7F690'SET context_info = @useridSELECT DISTINCT _station_idname, _recstocksite_idname, _product_idname , convert(int, SUM(_applyqty)) AS qty , convert(int, SUM(CASE

WHEN datepart(month, don) = 1 THEN _applyqty ELSE 0 END)) AS '1month' , convert(int, SUM(CASE

WHEN datepart(month, don) = 2 THEN _applyqty ELSE 0 END)) AS '2month' , convert(int, SUM(CASE

WHEN datepart(month, don) = 3 THEN _applyqty ELSE 0 END)) AS '3month' , convert(int, SUM(CASE

WHEN datepart(month, don) = 4 THEN _applyqty ELSE 0 END)) AS '4month' , convert(int, SUM(CASE

WHEN datepart(month, don) = 5 THEN _applyqty ELSE 0 END)) AS '5month' , convert(int, SUM(CASE

WHEN datepart(month, don) = 6 THEN _applyqty ELSE 0 END)) AS '6month' , convert(int, SUM(CASE

WHEN datepart(month, don) = 7 THEN _applyqty ELSE 0 END)) AS '7month' , convert(int, SUM(CASE

WHEN datepart(month, don) = 8 THEN _applyqty ELSE 0 END)) AS '8month' , convert(int, SUM(CASE

WHEN datepart(month, don) = 9 THEN _applyqty ELSE 0 END)) AS '9month' , convert(int, SUM(CASE

WHEN datepart(month, don) = 10 THEN _applyqty ELSE 0 END)) AS '10month' , convert(int, SUM(CASE

WHEN datepart(month, don) = 11 THEN _applyqty ELSE 0 END)) AS '11month' , convert(int, SUM(CASE

WHEN datepart(month, don) = 12 THEN _applyqty ELSE 0 END)) AS '12month'FROM Filterednew_zh_stockoutorder so, Filterednew_zh_stockoutorderdetail sod, Filterednew_srv_siteinv sWHERE _station_idname IN (@station) AND _product_idname IN (@productname) AND datepart(year, don) = @year AND _recstocksite_id = _stocksite_id AND _product_id = _product_id AND _zh_stockoutorderid = _stockoutorder_idGROUP BY _station_idname, _recstocksite_idname, _product_idname查询结果如下：