首页新闻找找看学习计划

SQl语句,三个表连接查询

0
[已解决问题] 解决于 2012-11-07 23:16

查找出型号表的所有型号,该型号共领料,和入库的个数

NothingHave的主页 NothingHave | 初学一级 | 园豆:6
提问于:2012-08-16 14:22
< >
分享
最佳答案
0

※我使用ORACLE语法写的,给你个参考:

 1 SELECT
 2   A.型号,
 3   NVL(B.领用个数SUM,0) AS 共领料,
 4   NVL(C.入库数量SUM,0) AS 共入库,
 5 FROM 型号表 A
 6 LEFT JOIN (
 7   SELECT SUBB.型号,SUM(SUBB.领用个数) AS 领用个数SUM
 8   FROM 领料表 SUBB
 9   GROUP BY 型号
10 ) B
11 ON B.型号 = A.型号
12 LEFT JOIN (
13   SELECT SUBC.型号,SUM(SUBC.入库数量) AS 入库数量SUM
14   FROM 入库表 SUBC
15   GROUP BY 型号
16 ) C
17 ON C.型号 = A.型号
18 ORDER BY A.型号 ASC
奖励园豆:5
三阶 | 小虾三级 |园豆:1436 | 2012-08-16 15:00
其他回答(2)
1

select 型号表.型号,领料表.领用个数,入库表.入库数量 from 型号表 left join 领料表 on 型号表.型号=领料表.型号 left join 入库表 on 型号表.型号=入库表.入库数量

(跟两个表联查是一样的)

淘@淘 | 园豆:582 (小虾三级) | 2012-08-16 14:35

这样查找数据有问题的~我就是这样写的

支持(1) 反对(2) NothingHave | 园豆:6 (初学一级) | 2012-08-16 14:37
0
create table tbla
(
    typename varchar(50)
)
insert into tbla
select 'dt-051' union all
select 'dt-052' union all
select 'dt-053' union all
select 'dt-054' union all
select 'dt-055' union all
select 'dt-056'


create table tblb
(
    typename varchar(50),
    num int,
)
insert into tblb
select 'dt-056',30 union all
select 'dt-056',20 union all
select 'dt-051',10 union all
select 'dt-052',30 union all
select 'dt-053',20 union all
select 'dt-051',30


create table tblc
(
    typename varchar(50),
    num int,
)
insert into tblc
select 'dt-056',30 union all
select 'dt-055',20 union all
select 'dt-056',20 union all
select 'dt-055',30 union all
select 'dt-051',50 union all
select 'dt-053',10


select a.typename as 型号,sum(isnull(b.num,0)) as 共领料,sum(isnull(c.num,0)) as 共入库 
from tbla as a 
left join tblb as b on b.typename = a.typename 
left join tblc as c on c.typename = a.typename 
group by a.typename
order by a.typename asc select a.typename,isnull(b.sumnum,0) as 工领料,isnull(c.sumnum,0) as 共入库 from tbla as a left join (select typename,sum(num) as sumnum from tblb group by typename) b on b.typename = a.typename left join (select typename,sum(num) as sumnum from tblc group by typename) c on c.typename = a.typename order by a.typename asc

/*
dt-051    40    50
dt-052    30    0
dt-053    20    10
dt-054    0    0
dt-055    0    50
dt-056    50    50
*/
暗尘掩月 | 园豆:183 (初学一级) | 2012-08-16 16:08
select a.typename as 型号,isnull(sum(b.num),0) as 共领料,isnull(sum(c.num),0) as 共入库 
from tbla as a
left join tblb as b on b.typename = a.typename
left join tblc as c on c.typename = a.typename
group by a.typename
order by a.typename asc

--这个查出来的结果
/*
dt-051    40    100
dt-052    30    0
dt-053    20    10
dt-054    0    0
dt-055    0    50
dt-056    100    100
*/

有问题 还没弄明白
支持(0) 反对(0) 暗尘掩月 | 园豆:183 (初学一级) | 2012-08-16 16:10
--提供另外一种写法
select
a.typename as 型号, isnull(bb.outqty,0) as '共领料', isnull(cc.inqty,0) as '共入库' from tbla as a outer apply(select sum(num) outqty from tblb as b where b.typename=a.typename) bb outer apply(select sum(num) inqty from tblc as c where c.typename=a.typename) cc
支持(0) 反对(0) 暗尘掩月 | 园豆:183 (初学一级) | 2012-08-16 17:35
select a.typename as 型号,isnull(b.num,0) as 共领料,isnull(c.num,0) as 共入库 
from tbla as a 
left join tblb as b on b.typename = a.typename 
left join tblc as c on c.typename = a.typename 


--多次left join 再汇总 会出现如下问题 
/*
dt-051    10    50
dt-051    30    50
dt-052    30    0
dt-053    20    10
dt-054    0    0
dt-055    0    20
dt-055    0    30
dt-056    30    30
dt-056    30    20
dt-056    20    30
dt-056    20    20
*/
支持(0) 反对(0) 暗尘掩月 | 园豆:183 (初学一级) | 2012-08-16 17:39
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册