咨询一个联合查询的问题

[julong88]

a表是库存
b表是销售
c表是进货

（有些物品可能不在库存表了但销售表里面可能会有，因为以前卖过）


用商品编码（BianMa）关联如何统计出进销存数据，放在一张表内显示

[dawugui]

select a.* , b.* , c.* from a
left join b on a.BianMa = b.BianMa
left join c on a.BianMa = c.BianMa

[julong88]

[Quote=引用 1 楼 dawugui 的回复:]
select a.* , b.* , c.* from a
left join b on a.BianMa = b.BianMa
left join c on a.BianMa = c.BianMa
[/Quote]

这样不行，我需要的是统计出来的数据

[dawugui]

select a.BianMa 商品编码, 
       isnull(b.value , 0) 销售 , 
       isnull(c.value , 0) 进货 ,
       isnull(c.value , 0) - isnull(b.value , 0) 库存
from a  
left join b on a.BianMa = b.BianMa
left join c on a.BianMa = c.BianMa

[飘零一叶]

[Quote=引用 2 楼 julong88 的回复:]
引用 1 楼 dawugui 的回复:
select a.* , b.* , c.* from a
left join b on a.BianMa = b.BianMa
left join c on a.BianMa = c.BianMa


这样不行，我需要的是统计出来的数据
[/Quote]
想要什么样的结果？麻烦给出点数据，和你想要的结果。

[julong88]

a表：Name BianMa KuCun（同一个编号只有一个记录）

b表：Name BianMa XiaoShouShuLiang（同一个编号可能会有多个记录）

c表：Name BianMa JinHuoShuLiang（同一个编号可能会有多个记录）

（有些物品可能不在库存表了但销售表里面可能会有，因为以前卖过）

需要统计出
Name BianMa KuCun XiaoShouShuLiang JinHuoShuLiang

统计值

[dawugui]

select a.* , 
       isnull(m.XiaoShouShuLiang, 0) XiaoShouShuLiang,
       isnull(n.JinHuoShuLiang, 0) JinHuoShuLiang
from a left join
(select Name ,  BianMa , sum(XiaoShouShuLiang) XiaoShouShuLiang from b group by Name ,  BianMa) m
on a.Name = a.Name and a.BianMa = m.BianMa left join
(select Name ,  BianMa , sum(JinHuoShuLiang) JinHuoShuLiang from b group by Name ,  BianMa) n
on a.Name = a.Name and n.BianMa = n.BianMa

[dawugui]

select a.* , 
       isnull(m.XiaoShouShuLiang, 0) XiaoShouShuLiang,
       isnull(n.JinHuoShuLiang, 0) JinHuoShuLiang
from a left join
(select Name ,  BianMa , sum(XiaoShouShuLiang) XiaoShouShuLiang from b group by Name ,  BianMa) m
on a.Name = a.Name and a.BianMa = m.BianMa left join
(select Name ,  BianMa , sum(JinHuoShuLiang) JinHuoShuLiang from c group by Name ,  BianMa) n
on a.Name = a.Name and n.BianMa = n.BianMa

[julong88]

[Quote=引用 6 楼 dawugui 的回复:]
SQL code
select a.* ,
isnull(m.XiaoShouShuLiang, 0) XiaoShouShuLiang,
isnull(n.JinHuoShuLiang, 0) JinHuoShuLiang
from a left join
(select Name , BianMa , sum(XiaoShouShuLiang) Xia……
[/Quote]

要的就是这个！非常感谢！


SQL没问题，Access好像不行

[aaseh]

我觉得也可以这样，你能试试看吗？试后给个结果我吧。

if OBJECT_ID('[a]') is not null drop table [a]
if OBJECT_ID('[b]') is not null drop table [b]
if OBJECT_ID('[c]') is not null drop table [c]

create table a(id int,aa int)
create table b(id int,bb int)
create table c(id int,cc int)

insert into a
select 1,1 union all
select 2,2 union all
select 3,3 union all
select 4,4 union all
select 5,5

insert into b
select 1,1 union all
select 2,2 union all
select 7,7 union all
select 8,8

insert into c
select 5,5

select t.id as 产品编码, case when t.a_aa IS NULL then 0 else t.a_aa end as a进货量, case when t.b_bb IS NULL then 0 else t.b_bb end as b库存量, case when c.cc IS NULL then 0 else c.cc end as c销售量 from
(select a.aa as a_aa, b.bb as b_bb, ISNULL(a.id,b.id) as id from a full outer join b on a.id=b.id) t
full outer join c on t.id=c.id

-------------------------------------------------------------------------
--产品编码 a进货量 b库存量 c销售量
--1	1	1	0
--2	2	2	0
--3	3	0	0
--4	4	0	0
--5	5	0	5
--7	0	7	0
--8	0	8	0