LEFT OUTER JOIN 多个表问题

表spxx  商品信息
id         spmc
1000001    统一冰红茶
1000002    康师傅绿茶

表xssp 销售商品
id   spid      sl  dj   zje
1    1000001   1   2.5  2.5

表jhsp 采购商品
id   spid      sl  dj   zje
1    1000001   1   2    2
2    1000001   1   2    2
3    1000001   1   2    2


SQL:SELECT spxx.spmc AS 商品名称, xssp.sl AS 销售数量, jhsp.sl AS 采购数量
FROM spxx LEFT OUTER JOIN
      jhsp ON spxx.id = jhsp.spid LEFT OUTER JOIN
      xssp ON spxx.id = xssp.spid

查询结果：


为什么销售商品数量是1，查询却有三次，如何得到下面的结果：

12 个解决方案

#1

写错了一个数 表jhsp 采购商品 是：
id   spid      sl  dj   zje 
1    1000001   1   2    2 
2    1000001   10  2    2 
3    1000001   1   2    2 

#2

表spxx  商品信息 
id         spmc 
1000001    统一冰红茶 
1000002    康师傅绿茶 

表xssp 销售商品 
id   spid      sl  dj   zje 
1    1000001   1   2.5  2.5 

表jhsp 采购商品 
id   spid      sl  dj   zje 
1    1000001   1   2    2 
2    1000001   1   2    2 
3    1000001   1   2    2 
---------------------------------------
select a.spmc,b.sl,c.sl from
spxx a 
left join (select spid,(select count(*) from  xssp  where xs.spid = spid and xs.id >=id ) id  from xssp xs) b  on a.spid = b.spid 
left join (select spid,(select count(*) from jhsp where jh.spid = spid and jh.id >=id ) id from jhsp jh) c  on a.spid = c.spid and b.id = c.id

#3

...

#4

create table spxx(spid int,         spmc  varchar(20))
insert spxx select 1000001,'统一冰红茶'
union all select 
1000002,'康师傅绿茶 '
create table xssp(id int ,  spid    int  ,sl  int)
insert xssp select 1,    1000001,   1  

create table jhsp(id int ,  spid  int,    sl int)
insert jhsp select 1,    1000001,   1  
union all select 2,    1000001 ,  10 
union all select 3 ,   1000001 ,  15 
---------------------------------------
select a.spmc,xs,jh from
spxx a 
left outer join
(select isnull(b.spid,c.spid) spid,b.sl xs,c.sl jh from
 (select spid,(select count(*) from  xssp  where xs.spid = spid and xs.id >=id ) id,sl  from xssp xs) b  
full outer   join
 (select spid,(select count(*) from jhsp where jh.spid = spid and jh.id >=id ) id,sl from jhsp jh) c  on b.id = c.id 
) xsjh on a.spid = xsjh.spid
/*
spmc                 xs          jh          
-------------------- ----------- ----------- 
统一冰红茶                1           1
统一冰红茶                NULL        10
统一冰红茶                NULL        15
康师傅绿茶                NULL        NULL

*/
drop table spxx,xssp,jhsp

#5

同一商品有几条采购和销售记录时,这样计算是不正确的..
应先合计销售表\采购表..然后再用left join 

#6

SELECT 
spxx.spmc AS 商品名称, 
(select sum(xssp.sl) from xssp where xssp.spid = spxx.id ) AS 销售数量, 
jhsp.sl AS 采购数量 
FROM spxx 
JOIN  jhsp 
ON spxx.id = jhsp.spid

上述语句可以产生你期望的结果，但是我感觉你真正的需求好像是在统计某种商品的销售和采购情况，这样你可以在你语句的基础上稍微作修改，应该更合理一点：

SELECT spxx.spmc AS 商品名称, sum(xssp.sl) AS 销售数量, sum(jhsp.sl) AS 采购数量 
FROM spxx LEFT OUTER JOIN 
      jhsp ON spxx.id = jhsp.spid LEFT OUTER JOIN 
      xssp ON spxx.id = xssp.spid 
group by spxx.spmc

希望对你有作用。。。

#7

人家楼主就想把销售和进货明细放在一起看看，也未尝不可呀！

#8

create table spxx(id varchar(10), spmc varchar(10))
insert into spxx values('1000001', '统一冰红茶') 
insert into spxx values('1000002', '康师傅绿茶') 
create table xssp(id int, spid varchar(10), sl int, dj decimal(18,1) , zje decimal(18,1)) 
insert into xssp values(1, '1000001', 1, 2.5, 2.5 )
create table jhsp(id int, spid varchar(10), sl int, dj int, zje int)
insert into jhsp values(1, '1000001', 1, 2, 2) 
insert into jhsp values(2, '1000001', 1, 2, 2) 
insert into jhsp values(3, '1000001', 1, 2, 2) 
go

select isnull(c.spmc,d.spmc) spmc , c.sl xssl, d.sl jhsl from
(
  select a.* , b.sl , b.px from spxx a
  left join
  (select t.* , px = (select count(1) from xssp where spid = t.spid and id < t.id) + 1 from xssp t) b
  on a.id = b.spid
  ) c
full join
(
  select m.spmc,n.sl , n.px from spxx m,
  (select t.* , px = (select count(1) from jhsp where spid = t.spid and id < t.id) + 1 from jhsp t) n
  where m.id = n.spid
) d
on c.spmc = d.spmc and c.px = d.px

/*
spmc       xssl        jhsl        
---------- ----------- ----------- 
统一冰红茶      1           1
统一冰红茶      NULL        1
统一冰红茶      NULL        1
康师傅绿茶      NULL        NULL

（所影响的行数为 4 行）
*/


drop table spxx , xssp , jhsp

#9

引用 6 楼 zbc1009 的回复:

SQL codeSELECT 
spxx.spmc AS 商品名称, 
(select sum(xssp.sl) from xssp where xssp.spid = spxx.id ) AS 销售数量, 
jhsp.sl AS 采购数量 
FROM spxx 
JOIN  jhsp 
ON spxx.id = jhsp.spid



上述语句可以产生你期望的结果，但是我感觉你真正的需求好像是在统计某种商品的销售和采购情况，这样你可以在你语句的基础上稍微作修改，应该更合理一点： 

SQL codeSELECT spxx.spmc AS 商品名称, sum(xssp.sl) AS 销…

结果还是：

#10

create table spxx(id varchar(10), spmc varchar(10))
insert into spxx values('1000001', '统一冰红茶') 
insert into spxx values('1000002', '康师傅绿茶') 
create table xssp(id int, spid varchar(10), sl int, dj decimal(18,1) , zje decimal(18,1)) 
insert into xssp values(1, '1000001', 1, 2.5, 2.5 )
create table jhsp(id int, spid varchar(10), sl int, dj int, zje int)
insert into jhsp values(1, '1000001', 1, 2, 2) 
insert into jhsp values(2, '1000001', 10, 2, 2) 
insert into jhsp values(3, '1000001', 1, 2, 2) 
go

select c.spmc,b.sl,a.sl
from (select *,px = (select count(1) from jhsp where id <= b.id) from jhsp b) a
left join (select *,px = (select count(1) from xssp where id <= b.id) from xssp b) b on b.id = a.px 
left join spxx c on a.spid = c.id

drop table spxx,xssp,jhsp

/*
spmc       sl          sl          
---------- ----------- ----------- 
统一冰红茶      1           1
统一冰红茶      NULL        10
统一冰红茶      NULL        1
*/

#11

select jh.a as 名称,jh.b as 销售数量,xs.b as 进货数量 from
(select spxx.spmc a,sum(isnull(jhsp.sl,0)) b
from spxx left outer join jhsp on spxx.spid=jhsp.spid  
group by spxx.spmc ) jh,

(select spxx.spmc a ,sum(isnull(xssp.sl,0)) b 
from spxx left outer join xssp on  spxx.spid=xssp.spid 
group by spxx.spmc ) xs 
where jh.a=xs.a

#12

关注一下，又一下