34,590
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#入库表') is null
drop table #入库表
Go
Create table #入库表([日期] Date,[工单号] nvarchar(26),[收货方] nvarchar(22),[体积] int)
Insert #入库表
select '2017/3/1',N'A30001',N'AA',25 union all
select '2017/3/1',N'A30002',N'BB',35 union all
select '2017/3/1',N'A30003',N'BB',35 union all
select '2017/3/2',N'A30003',N'BB',35
GO
if not object_id(N'Tempdb..#出库表') is null
drop table #出库表
Go
Create table #出库表([日期] Date,[工单号] nvarchar(26),[收货方] nvarchar(22),[体积] int)
Insert #出库表
select '2017/3/1',N'B30001',N'AA',15 union all
select '2017/3/1',N'B30002',N'BB',25 union all
select '2017/3/1',N'B30003',N'BB',5
Go
--测试数据结束
DECLARE @sql VARCHAR(MAX);
SET @sql = 'select 日期';
SELECT @sql
= @sql + ',sum(case 收货方 when ''' + 收货方 + ''' then 入库体积 else 0 end)[' + 收货方 + '入库体积]' + ',sum(case 收货方 when '''
+ 收货方 + ''' then 出库体积 else 0 end)[' + 收货方 + '出库体积]'
FROM
(
SELECT DISTINCT
收货方
FROM
(
SELECT t1.*,
t2.出库体积
FROM
(SELECT 日期, 收货方, SUM(体积) AS 入库体积 FROM #入库表 GROUP BY 日期, 收货方) t1
LEFT JOIN
(SELECT 日期, 收货方, SUM(体积) AS 出库体积 FROM #出库表 GROUP BY 日期, 收货方) t2
ON t2.日期 = t1.日期
AND t2.收货方 = t1.收货方
) tab1
) a;
SET @sql
= @sql
+ ' from (SELECT t1.*,
t2.出库体积
FROM
(SELECT 日期, 收货方, SUM(体积) AS 入库体积 FROM #入库表 GROUP BY 日期, 收货方) t1
LEFT JOIN
(SELECT 日期, 收货方, SUM(体积) AS 出库体积 FROM #出库表 GROUP BY 日期, 收货方) t2
ON t2.日期 = t1.日期
AND t2.收货方 = t1.收货方)tab1 group by 日期';
EXEC (@sql);