select a.品名编号,进货数量,进货价,退货数量,退货价 from
(select 品名编号,sum(进货数量) 进货数量,sum(进货价) 进货价 group by 品名编号) a,
(select 品名编号,sum(退货数量) 退货数量,sum(退货价) 退货价 group by 品名编号) b
where a.品名编号=b.品名编号
(select 品名编号,sum(进货数量) 进货数量,sum(进货价) 进货价 group by 品名编号) a,
(select 品名编号,sum(退货数量) 退货数量,sum(退货价) 退货价 group by 品名编号) b
where a.品名编号=b.品名编号
select a.品名编号,进货数量,进货价,退货数量,退货价 from
(select 品名编号,sum(进货数量) 进货数量,sum(进货价) 进货价 group by 品名编号) a left join (select 品名编号,sum(退货数量) 退货数量,sum(退货价) 退货价 group by 品名编号) b
on a.品名编号=b.品名编号
from(
select 品名编号,进货数量=sum(进货数量),进货价=sum(进货价) from 进货记录 group by 品名编号
) a left join (
select 品名编号,退货数量=sum(退货数量),退货价=sum(退货价) from 退货记录 group by 品名编号
) b on a.品名编号=b.品名编号
(select 品名编号,sum(进货数量) 进货数量,sum(进货价) 进货价 group by 品名编号) a
left outer join
(select 品名编号,sum(退货数量) 退货数量,sum(退货价) 退货价 group by 品名编号) b
on a.品名编号=b.品名编号
select a.pmbh,sumjh,jhdj,b.sumth,b.thdj
from
(select pmbh,sum(jhsl) as sumjh,jhdj from jh group by pmbh) a left join
(select pmbh,sum(thsl) as sumth,thdj from th gorup by pmbh) b on a.pmbh=b.pmbh
declare @进货记录 table(品名编号 varchar(6),进货数量 int,进货价 int)
insert into @进货记录
select '00001',10,200
union all select '00001',20,200
union all select '00002',15,100
union all select '00003',5,50declare @退货记录 table(品名编号 varchar(6),退货数量 int,退货价 int)
insert into @退货记录
select '00001',5,180
union all select '00001',2,180
union all select '00003',3,50--查询
select a.品名编号,进货数量,进货价,退货数量,退货价
from(
select 品名编号,进货数量=sum(进货数量),进货价=sum(进货价) from @进货记录 group by 品名编号
) a left join (
select 品名编号,退货数量=sum(退货数量),退货价=sum(退货价) from @退货记录 group by 品名编号
) b on a.品名编号=b.品名编号/*--测试结果
品名编号 进货数量 进货价 退货数量 退货价
------ ----------- ----------- ----------- -----------
00001 30 400 7 360
00002 15 100 NULL NULL
00003 5 50 3 50(所影响的行数为 3 行)
--*/
from
进货记录 a left join 退货记录 b
on a.品名编号 = b.品名编号
group by a.品名编号,a.进货价,b.退货价
thsl=(select sum(thsl) thsl from th where th.pmbh=jh.pmbh),
thdjj=(select top 1 thdjj from th where th.pmbh=jh.pmbh)
from jh
group by pmbh,jhdj
left join (select 品名编号,sum(退货数量) 退货数量,退货价 from 进货记录 group by 品名编号,退货价) b
on a.品名编号=b.品名编号
declare @进货记录 table(品名编号 varchar(6),进货数量 int,进货价 int)
insert into @进货记录
select '00001',10,200
union all select '00001',20,200
union all select '00002',15,100
union all select '00003',5,50declare @退货记录 table(品名编号 varchar(6),退货数量 int,退货价 int)
insert into @退货记录
select '00001',5,180
union all select '00001',2,180
union all select '00003',3,50select a.品名编号,sum(进货数量) 进货数量,a.进货价,
isnull(sum(b.退货数量),0) 退货数量,isnull(b.退货价,0) 退货价
from
进货记录 a left join 退货记录 b
on a.品名编号 = b.品名编号
group by a.品名编号,a.进货价,b.退货价
品名编号进货数量 进货价 退货数量 退货价
------ ----------- ----------- ----------- -----
00001 60 200 14 180
00002 15 100 0 0
00003 5 50 3 50
--测试数据
declare @进货记录 table(品名编号 varchar(6),进货数量 int,进货价 int)
insert into @进货记录
select '00001',10,200
union all select '00001',20,200
union all select '00002',15,100
union all select '00003',5,50declare @退货记录 table(品名编号 varchar(6),退货数量 int,退货价 int)
insert into @退货记录
select '00001',5,180
union all select '00001',2,180
union all select '00003',3,50select a.品名编号,进货数量,进货价,isnull(退货数量,0) 退货数量,isnull(退货价,0) 退货价
from(
select 品名编号,进货数量=sum(进货数量),进货价 from @进货记录 group by 品名编号,进货价
) a left join (
select 品名编号,退货数量=sum(退货数量),退货价 from @退货记录 group by 品名编号,退货价
) b on a.品名编号=b.品名编号品名编号 进货数量 进货价 退货数量 退货价
------ ----------- ----------- ----------- -----------
00001 30 200 7 180
00002 15 100 0 0
00003 5 50 3 50(所影响的行数为 3 行)