一张表T1(合同计划表)
htbh cpbh pc
a001 b001 1
a001 b002 2
另一张表T2(生产进度表)htbh cpbh pc xs
a001 b001 1 10
a001 b002 2 20
a001 b002 2 30htbh 是合同编号 cpbh是合同编号pc是生产批次
如何查询得到结果:就是想同一个合同号,同一个货号,同一批次的生产进度数量加起来得到这个组合的报表
htbh cpbh pc z_xs(总箱数)
a001 b001 1 10
a001 b002 2 50是否可用left jion 把两表合起来?
htbh cpbh pc
a001 b001 1
a001 b002 2
另一张表T2(生产进度表)htbh cpbh pc xs
a001 b001 1 10
a001 b002 2 20
a001 b002 2 30htbh 是合同编号 cpbh是合同编号pc是生产批次
如何查询得到结果:就是想同一个合同号,同一个货号,同一批次的生产进度数量加起来得到这个组合的报表
htbh cpbh pc z_xs(总箱数)
a001 b001 1 10
a001 b002 2 50是否可用left jion 把两表合起来?
insert into @t select 'a001','b001',1
union all select 'a001','b002',2declare @a table(htbh varchar(10),cpbh varchar(10),pc int,xs int)
insert into @a select 'a001','b001',1,10
union all select 'a001','b002',2,20
union all select 'a001','b002',2,30select a.htbh,
a.cpbh,
a.pc,
sum(b.xs) as z_xs
from @t a,@a b
where
a.htbh*=b.htbh and
a.cpbh*=b.cpbh
group by
a.htbh,a.cpbh,a.pc
T1.htbh,
T1.cpbh,
T1.pc,
IsNull(SUM(B.xs),0) As z_xs
From T1
Left Join T2
On T1.htbh=T2.htbh And T1.cpbh=T2.cpbh And T1.pc=T2.pc
Group By T1.htbh,T1.cpbh,T1.pc
Order By T1.htbh,T1.cpbh,T1.pc
(select sum(xs) from T2 where htbh=T1.htbh,cpbh=T1.cpbh,pc=T1.pc)
from T1
order by htbh,cpbh,pc
declare @t table(htbh varchar(10),cpbh varchar(10),pc int)
insert into @t select 'a001','b001',1
union all select 'a001','b002',2
union all select 'a001','b003',3declare @a table(htbh varchar(10),cpbh varchar(10),pc int,xs int)
insert into @a select 'a001','b001',1,10
union all select 'a001','b002',2,20
union all select 'a001','b002',2,30select a.htbh,
a.cpbh,
a.pc,
isnull(sum(b.xs),0) as z_xs
from @t a,@a b
where
a.htbh*=b.htbh and
a.cpbh*=b.cpbh and
a.pc*=b.pc
group by
a.htbh,a.cpbh,a.pc
insert into @t select 'a001','b001',1
union all select 'a001','b002',2declare @a table(htbh varchar(10),cpbh varchar(10),pc int,xs int)
insert into @a select 'a001','b001',1,10
union all select 'a001','b002',2,20
union all select 'a001','b002',2,30select T.htbh,T.cpbh,T.pc,T1.xs from
@t T left join
(select htbh,cpbh,pc,sum(xs) as xs from @a group by htbh,cpbh,pc) T1 on
T.htbh=T1.htbh and T.cpbh=T1.cpbh and T.pc=T1.pc
insert into @t select 'a001','b001',1
union all select 'a001','b002',2declare @a table(htbh varchar(10),cpbh varchar(10),pc int,xs int)
insert into @a select 'a001','b001',1,10
union all select 'a001','b002',2,20
union all select 'a001','b002',2,30
select max(t2.htbh)as htbh,t2.cpbh as cpbh,max(t2.pc)as pc, sum(t2.xs)as z_xs
from @t t1
join @a t2
on t2.cpbh=t1.cpbh
group by t2.cpbh
T1.htbh,
T1.cpbh,
T1.pc,
IsNull(SUM(B.xs),0) As z_xs
From T1
Left Join T2
On T1.htbh=T2.htbh And T1.cpbh=T2.cpbh And T1.pc=T2.pc
Where A.cpbh='b002'
Group By T1.htbh,T1.cpbh,T1.pc
Order By T1.htbh,T1.cpbh,T1.pc
htbh cpbh pc z_xs(总箱数)gcbh
a001 b001 1 10 A
a001 b002 2 50 B
T1.htbh,
T1.cpbh,
T1.pc,
IsNull(SUM(B.xs),0) As z_xs,
T1.gcbh
From T1
Left Join T2
On T1.htbh=T2.htbh And T1.cpbh=T2.cpbh And T1.pc=T2.pc
Where A.cpbh='b002'
Group By T1.htbh,T1.cpbh,T1.pc,T1.gcbh
Order By T1.htbh,T1.cpbh,T1.pc,T1.gcbh