34,576
社区成员
发帖
与我相关
我的任务
分享
select typename,sum(ysum) ysum,sum(xsum) xsum ,section from
(select typename,0 ysum,xsum,section from info_1
union all
select typename,ysum,0 xsum from info_2
)
group by
type_name,section
select [type_name],sum(xsum),sum(ysum),[section] from
(
select [type_name]=case when a.[type_name] is null then b.[type_name] else a.[type_name] end,
xsum=isnull(a.xsum,0),
ysum=isnull(b.ysum,0),
[section]=case when a.[section] is null then b.[section] else a.[section] end
from info_1 a
full join info_2 b on a.[type_name]=b.[type_name]
) a
group by [type_name],[section]
--> 测试数据: @type
declare @type table (type_id int,type_name varchar(4))
insert into @type
select 1,'蒙数' union all
select 2,'蒙阅' union all
select 3,'英语' union all
select 4,'政治' union all
select 5,'语文'
--> 测试数据: @info_1
declare @info_1 table (type_name varchar(4),xsum int,section varchar(4))
insert into @info_1
select '蒙数',2,'华中' union all
select '蒙阅',4,'华中' union all
select '蒙阅',1,'西北'
--> 测试数据: @info_2
declare @info_2 table (type_name varchar(4),ysum int,section varchar(4))
insert into @info_2
select '蒙数',1,'华南' union all
select '蒙阅',1,'华南' union all
select '英语',3,'华南' union all
select '语文',2,'华南' union all
select '蒙阅',4,'华中' union all
select '蒙数',3,'西北' union all
select '蒙阅',1,'西北'
select type_name=isnull(a.type_name,b.type_name),ysum=isnull(ysum,0),xsum=isnull(xsum,0),section=isnull(a.section,b.section) from @info_1 a full join @info_2 b
on a.type_name=b.type_name and a.section=b.section
select [type_name]=case when a.[type_name] is null then b.[type_name] else a.[type_name] end,
xsum=isnull(a.xsum,0),
ysum=isnull(b.ysum,0),
section=case when a.[section] is null then b.[section] else a.[section] end
from info_1 a,info_2 b
where a.[type_name]=b.[type_name]
select typename,sum(ysum) ysum,sum(xsum) xsum ,section from
(select typename,ysum,0 xsum,section from info_1
union all
select * from info_2
)
group by
type_name,section