SQl语句，三个表连接查询
0
[已解决问题]
解决于 2012-11-07 23:16
查找出型号表的所有型号，该型号共领料，和入库的个数
最佳答案
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
其他回答(2)
1
select 型号表.型号,领料表.领用个数,入库表.入库数量 from 型号表 left join 领料表 on 型号表.型号=领料表.型号 left join 入库表 on 型号表.型号=入库表.入库数量
(跟两个表联查是一样的)
这样查找数据有问题的~我就是这样写的
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
*/
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
*/
有问题 还没弄明白
--提供另外一种写法
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
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 */