 加好友  发短信
等级：超级版主
帖子：109474
积分：557032
威望：0
精华：9
注册：2015/6/24 9:21:00
|
 Post By：2024/2/4 11:01:00 [显示全部帖子]
到数据库里建2个视图视图1 select un寄库,unfamily,sum(盒数) as 盒数 from (select CONVERT(nvarchar(10), un寄库, 120 ) As un寄库,unfamily ,盒数,dateDiff(month,un寄库,getdate()) as 月数 from 成品数据 ) as a where 月数 < 3 group by un寄库,unfamily
视图2 select de出库时间,unfamily,sum(de发货数量) as 出货盒数 from (select CONVERT(nvarchar(10), de出库时间, 120 ) As de出库时间,unfamily ,de发货数量,dateDiff(month,de出库时间,getdate()) as 月数 from 发货记录 ) as a where 月数 < 3 group by de出库时间,unfamily
然后使用sql生成表格
select year(a.un寄库) as 年,month(a.un寄库) as 月,day(a.un寄库) as 日,盒数,出货盒数,a.unfamily,b.unfamily as 出库unfamily from 视图1 as a inner join 视图2 as b on a.un寄库 = b.de出库时间 union all select year(un寄库) as 年,month(un寄库) as 月,day(un寄库) as 日,盒数,0 as 出货盒数,unfamily,'' as 出库unfamily from 视图1 where un寄库 not in (select de出库时间 from 视图2) union all select year(de出库时间) as 年,month(de出库时间) as 月,day(de出库时间) as 日,0 as 盒数,出货盒数,'' as unfamily,unfamily as 出库unfamily from 视图2 where de出库时间 not in (select un寄库 from 视图1) [此贴子已经被作者于2024/2/4 11:09:19编辑过]
|
|
