专家坐堂-inner join 合并求助Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

视图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)