有两个表A和表B
A res_code,res_name,res_need_num B res_code,res_name,res_use_num
LDPT01, 陆地平台01,10 LDPT01, 陆地平台01,15
LDPT02, 陆地平台02,20 LDPT02, 陆地平台02,25
LDPT02, 陆地平台02,15 LDPT01, 陆地平台01,40 想按res_code统计总的需求量和使用量,想达到的效果是res_code,res_name,res_need_num,res_use_num
LDPT01 陆地平台01 10 55
LDPT02 陆地平台02 30 15大家帮我想想办法,谢谢!
A res_code,res_name,res_need_num B res_code,res_name,res_use_num
LDPT01, 陆地平台01,10 LDPT01, 陆地平台01,15
LDPT02, 陆地平台02,20 LDPT02, 陆地平台02,25
LDPT02, 陆地平台02,15 LDPT01, 陆地平台01,40 想按res_code统计总的需求量和使用量,想达到的效果是res_code,res_name,res_need_num,res_use_num
LDPT01 陆地平台01 10 55
LDPT02 陆地平台02 30 15大家帮我想想办法,谢谢!
LDPT02 陆地平台02 35 25??
from a,b
where a.res_code = b.res_code and a.res_name = b.res_name
group by a.res_code,b.res_name
isnull(a.res_name , b.res_name) res_name,
isnull(sum(a.res_need_num),0) res_need_num ,
isnull(sum(b.res_use_num),0) res_use_num
from a full join b
on a.res_code = b.res_code and a.res_name = b.res_name
group by a.res_code,b.res_name
(select res_code,res_name,sum(res_need_num)res_need_num from A group by res_code,res_name) TabA
left join (select res_code,res_name,sum(res_use_num)res_use_num from B group by res_code,res_name)TabB
on TabA.res_code=TabB.res_code
A res_code,res_name,res_need_num B res_code,res_name,res_use_num
LDPT01, 陆地平台01,10 LDPT01, 陆地平台01,15
LDPT02, 陆地平台02,20 LDPT02, 陆地平台02,25
LDPT02, 陆地平台02,15 LDPT01, 陆地平台01,40 想按res_code统计总的需求量和使用量,想达到的效果是res_code,res_name,res_need_num,res_use_num
LDPT01 陆地平台01 10 55
LDPT02 陆地平台02 35 25 谢谢dawugui,不过你说的那个方法我有点问题,比如A.res_code = LDPT02 时,如果用 a.res_code = b.res_code 在B表中就会统计出两条记录
LDPT02, 陆地平台02,25 再用SUM时结果就会是50 ,我就是被这个问题烦的
if object_id('tempdb.dbo.#A') is not null drop table #A
create table #A (res_code varchar(11),res_name varchar(11),res_need_num int)
insert into #A
select 'LDPT01','陆地平台01',10 union all
select 'LDPT02','陆地平台02',20 union all
select 'LDPT02','陆地平台02',15
--> 测试数据: #B
if object_id('tempdb.dbo.#B') is not null drop table #B
create table #B (res_code varchar(11),res_name varchar(11),res_use_num int)
insert into #B
select 'LDPT01','陆地平台01',15 union all
select 'LDPT02','陆地平台02',25 union all
select 'LDPT01','陆地平台01',40select
res_code=isnull(a.res_code,b.res_code),
res_name=isnull(a.res_name,b.res_name),
res_need_num=isnull(a.res_need_num,0),
res_use_num=isnull(res_use_num,0)
from
(select res_code,res_name,sum(res_need_num)res_need_num from #A group by res_code,res_name) a
full join
(select res_code,res_name,sum(res_use_num)res_use_num from #B group by res_code,res_name) b
on a.res_code=b.res_code and a.res_name=b.res_name/*
res_code res_name res_need_num res_use_num
----------- ----------- ------------ -----------
LDPT01 陆地平台01 10 55
LDPT02 陆地平台02 35 25
*/