多表统计表1 jilu
idbs leibie feiyong
1001 第一批任务 800
1002 第二批任务 200
1003 第三批任务 990
1004 第四批任务 80表2 mingxiidbs changpin shuliang
1001 产品a 5
1003 产品b 15
1001 产品c 3
1002 产品d 22
1004 产品e 7
1004 产品f 59
1001 产品g 32
1002 产品h 123需要统计出所有任务的所有费用和数量(就是想知道表1的总费用和表2的总数量)
idbs leibie feiyong
1001 第一批任务 800
1002 第二批任务 200
1003 第三批任务 990
1004 第四批任务 80表2 mingxiidbs changpin shuliang
1001 产品a 5
1003 产品b 15
1001 产品c 3
1002 产品d 22
1004 产品e 7
1004 产品f 59
1001 产品g 32
1002 产品h 123需要统计出所有任务的所有费用和数量(就是想知道表1的总费用和表2的总数量)
join mingxi b on a.idbs=b.idbs
gropu by a.idbs
Select jilu.idbs,leibie,feiyong,Sum(shuliang)
From jilu,mingxi
Where jilu.idbs=mingxi.idbs
Group by jilu.idbs,leibie,feiyong
(
idbs int,
leibie varchar(20),
feiyong int
)
insert into #jilu select 1001,'第一批任务',800
union all select 1002,'第二批任务',200
union all select 1003,'第三批任务',990
union all select 1004,'第四批任务',80
create table #mingxi
(
idbs int,
changpin varchar(20),
shuliang int
)
insert into #mingxi select 1001,'产品a',5
union all select 1003,'产品b',15
union all select 1001,'产品c',3
union all select 1002,'产品d',22
union all select 1004,'产品e',7
union all select 1004,'产品f',59
union all select 1001,'产品g',32
union all select 1002,'产品h',123select s.idbs,s.shuliang,j.feiyong
from #jilu j join
(
select idbs,sum(shuliang) shuliang from #mingxi group by idbs
)s on j.idbs=s.idbsidbs shuliang feiyong
----------- ----------- -----------
1001 40 800
1002 145 200
1003 15 990
1004 66 80(4 行受影响)
(
idbs int,
leibie varchar(20),
feiyong int
)
insert into #jilu select 1001,'第一批任务',800
union all select 1002,'第二批任务',200
union all select 1003,'第三批任务',990
union all select 1004,'第四批任务',80
create table #mingxi
(
idbs int,
changpin varchar(20),
shuliang int
)
insert into #mingxi select 1001,'产品a',5
union all select 1003,'产品b',15
union all select 1001,'产品c',3
union all select 1002,'产品d',22
union all select 1004,'产品e',7
union all select 1004,'产品f',59
union all select 1001,'产品g',32
union all select 1002,'产品h',123select s.idbs,s.shuliang,j.feiyong*cc as feiyong
from #jilu j join
(
select idbs,sum(shuliang) shuliang,count(*) cc from #mingxi group by idbs
)s on j.idbs=s.idbs
idbs shuliang
----------- ----------- -----------
1001 40 2400
1002 145 400
1003 15 990
1004 66 160(4 行受影响)
go
create table jilu(idbs int,leibie varchar(20),feiyong int)
insert into jilu select 1001,'第一批任务',800
union all select 1002,'第二批任务',200
union all select 1003,'第三批任务',990
union all select 1004,'第四批任务',80
go
if object_id('mingxi') is not null drop table mingxi
go
create table mingxi(idbs int,changpin varchar(20),shuliang int)
insert into mingxi select 1001,'产品a',5
union all select 1003,'产品b',15
union all select 1001,'产品c',3
union all select 1002,'产品d',22
union all select 1004,'产品e',7
union all select 1004,'产品f',59
union all select 1001,'产品g',32
union all select 1002,'产品h',123
go
select a.idbs,sum(a.feiyong) feiyong,sum(isnull(b.shuliang,0)) shuliang
from jilu a
left join mingxi b
on a.idbs =b.idbs
group by a.idbs
/*
idbs feiyong shuliang
----------- ----------- -----------
1001 2400 40
1002 400 145
1003 990 15
1004 160 66(4 行受影响)
*/