27,582
社区成员




SELECT #a.所属单位 ,
#a.年份 ,
SUM(ISNULL(#a.工资, 0)) AS 工资总额 ,
SUM(ISNULL(#b.福利, 0)) AS 福利总额 ,
SUM(ISNULL(#c.扣款金额, 0)) AS 个人扣款总额 ,
SUM(ISNULL(#d.扣款金额, 0)) AS 单位扣款总额 ,
SUM(ISNULL(#a.工资, 0)) + SUM(ISNULL(#b.福利, 0)) + SUM(ISNULL(#c.扣款金额, 0))
+ SUM(ISNULL(#d.扣款金额, 0)) AS 总计,
STUFF(( SELECT ',' + 扣款原因
FROM #d
WHERE 所属单位 = #a.所属单位
AND 年份 = #a.年份
FOR
XML PATH('')
), 1, 1, '') AS 扣款原因
FROM #a
LEFT JOIN #b ON #b.年份 = #a.年份
AND #b.月份 = #a.月份
AND #b.所属单位 = #a.所属单位
LEFT JOIN #c ON #c.年份 = #a.年份
AND #c.月份 = #a.月份
AND #c.所属单位 = #a.所属单位
LEFT JOIN #d ON #d.年份 = #a.年份
AND #d.月份 = #a.月份
AND #d.所属单位 = #a.所属单位
GROUP BY #a.所属单位 ,
#a.年份
--测试数据
if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([员工编号] nvarchar(27),[所属单位] nvarchar(21),[年份] nvarchar(21),[月份] int,[工资] int)
Insert #a
select N'BH12345',N'A','2018',1,'3000' union all
select N'BH12345',N'A','2018',2,'3001' union all
select N'BH12346',N'B','2018',1,'3000'
GO
if not object_id(N'Tempdb..#b') is null
drop table #b
Go
Create table #b([员工编号] nvarchar(27),[所属单位] nvarchar(21),[年份] nvarchar(21),[月份] int,[福利] int)
Insert #b
select N'BH12345',N'A','2018',1,1000 union all
select N'BH12345',N'A','2018',2,1001 union all
select N'BH12346',N'B','2018',1,890
GO
if not object_id(N'Tempdb..#c') is null
drop table #c
Go
Create table #c([员工编号] nvarchar(27),[所属单位] nvarchar(21),[年份] nvarchar(21),[月份] int,[扣款金额] int,[扣款原因] nvarchar(22))
Insert #c
select N'BH12345',N'A','2018',1,1000,N'房租' union all
select N'BH12345',N'A','2018',2,1001,N'房租' union all
select N'BH12346',N'B','2018',1,890,N'房租'
GO
if not object_id(N'Tempdb..#d') is null
drop table #d
Go
Create table #d([所属单位] nvarchar(21),[年份] nvarchar(21),[月份] int,[扣款金额] int,[扣款原因] nvarchar(22))
Insert #d
select N'A','2018',1,1000,N'房租' union all
select N'A','2018',2,1001,N'房租' union all
select N'B','2018',1,890,N'房租'
Go
--测试数据结束
SELECT #a.所属单位 ,
#a.年份 ,
#a.月份 ,
SUM(ISNULL(#a.工资, 0)) AS 工资总额 ,
SUM(ISNULL(#b.福利, 0)) AS 福利总额 ,
SUM(ISNULL(#c.扣款金额, 0)) AS 个人扣款总额 ,
SUM(ISNULL(#d.扣款金额, 0)) AS 单位扣款总额 ,
SUM(ISNULL(#a.工资, 0)) + SUM(ISNULL(#b.福利, 0)) + SUM(ISNULL(#c.扣款金额, 0))
+ SUM(ISNULL(#d.扣款金额, 0)) AS 总计
FROM #a
LEFT JOIN #b ON #b.年份 = #a.年份
AND #b.月份 = #a.月份
AND #b.所属单位 = #a.所属单位
LEFT JOIN #c ON #c.年份 = #a.年份
AND #c.月份 = #a.月份
AND #c.所属单位 = #a.所属单位
LEFT JOIN #d ON #d.年份 = #a.年份
AND #d.月份 = #a.月份
AND #d.所属单位 = #a.所属单位
WHERE #a.月份 = 1
GROUP BY #a.所属单位 ,
#a.年份 ,
#a.月份
--测试数据
if not object_id(N'Tempdb..#a') is null
drop table #a
Go
Create table #a([员工编号] nvarchar(27),[所属单位] nvarchar(21),[年份] nvarchar(21),[月份] int,[工资] int)
Insert #a
select N'BH12345',N'A','2018',1,'3000' union all
select N'BH12345',N'A','2018',2,'3001' union all
select N'BH12346',N'B','2018',1,'3000'
GO
if not object_id(N'Tempdb..#b') is null
drop table #b
Go
Create table #b([员工编号] nvarchar(27),[所属单位] nvarchar(21),[年份] nvarchar(21),[月份] int,[福利] int)
Insert #b
select N'BH12345',N'A','2018',1,1000 union all
select N'BH12345',N'A','2018',2,1001 union all
select N'BH12346',N'B','2018',1,890
GO
if not object_id(N'Tempdb..#c') is null
drop table #c
Go
Create table #c([员工编号] nvarchar(27),[所属单位] nvarchar(21),[年份] nvarchar(21),[月份] int,[扣款金额] int,[扣款原因] nvarchar(22))
Insert #c
select N'BH12345',N'A','2018',1,1000,N'房租' union all
select N'BH12345',N'A','2018',2,1001,N'房租' union all
select N'BH12346',N'B','2018',1,890,N'房租'
GO
if not object_id(N'Tempdb..#d') is null
drop table #d
Go
Create table #d([所属单位] nvarchar(21),[年份] nvarchar(21),[月份] int,[扣款金额] int,[扣款原因] nvarchar(22))
Insert #d
select N'A','2018',1,1000,N'房租' union all
select N'A','2018',2,1001,N'房租' union all
select N'B','2018',1,890,N'房租'
Go
--测试数据结束
SELECT #a.所属单位 ,
#a.年份 ,
#a.月份 ,
SUM(ISNULL(#a.工资, 0)) AS 工资总额 ,
SUM(ISNULL(#b.福利, 0)) AS 福利总额 ,
SUM(ISNULL(#c.扣款金额, 0)) AS 个人扣款总额 ,
SUM(ISNULL(#d.扣款金额, 0)) AS 单位扣款总额
FROM #a
LEFT JOIN #b ON #b.年份 = #a.年份
AND #b.月份 = #a.月份
LEFT JOIN #c ON #c.年份 = #a.年份
AND #c.月份 = #b.月份
LEFT JOIN #d ON #d.年份 = #a.年份
AND #d.月份 = #a.月份
WHERE #a.月份=1
GROUP BY #a.所属单位 ,
#a.年份 ,
#a.月份