今天练的题出自SQL数据库开发微信公众号，链接：mp.weixin.qq.com/s/QAOwbZwR6…

题目

测试数据

CREATE TABLE G1102A  
(WAREHOUSE VARCHAR(10),  
 ITEM VARCHAR(10),  
 QTY INT);
   
INSERT INTO G1102A VALUES ('A','P001',100);  
INSERT INTO G1102A VALUES ('A','P002',200);  
INSERT INTO G1102A VALUES ('B','P001',120 );  
   
CREATE TABLE G1102B  
(WAREHOUSE VARCHAR(10),  
 ITEM VARCHAR(10),  
 QTY INT  
);
   
INSERT INTO G1102B VALUES ('A','P001',50);  
INSERT INTO G1102B VALUES ('B','P001',30);  
  
CREATE TABLE G1102C  
(WAREHOUSE VARCHAR(10),  
 ITEM VARCHAR(10),  
 QTY INT  
);
   
INSERT INTO G1102C VALUES ('A','P001',10);  
INSERT INTO G1102C VALUES ('A','P002',20);  
INSERT INTO G1102C VALUES ('C','P001',15);  
INSERT INTO G1102C VALUES ('C','P003',10);

解题

这道题没啥好说的，比较简单，考察的重点在join，因为MySQL没有full outer join，这里用left outer join union right outer join来实现，需要注意的是A、C两个表之间的物品是有差别的，所以左右连接需要对应取值。 结存=期初-发出+收入

select
    tbh.WAREHOUSE as '仓库',
    tbh.ITEM as '产品',
    ifnull(tbh.qty1, 0) as '期初',
    ifnull(tbc.qty2, 0) as '发出',
    ifnull(tbh.qty3, 0) as '收入',
    ifnull(tbh.qty1, 0)-ifnull(tbc.qty2, 0)+ifnull(tbh.qty3, 0) as '结存'
from
    (select 
        tba.WAREHOUSE,
        tba.ITEM,
        ifnull(tba.QTY, 0) as qty1,
        ifnull(tbb.QTY, 0) as qty2
    from G1102A as tba left outer join G1102B as tbb on tba.WAREHOUSE=tbb.WAREHOUSE and tba.ITEM=tbb.ITEM) as tbc
right outer join
    (select
        tbd.WAREHOUSE,
        tbd.ITEM,
        ifnull(tbd.QTY, 0) as qty1,
        ifnull(tbe.QTY, 0) as qty3
    from G1102A as tbd left outer join G1102C as tbe on tbd.WAREHOUSE=tbe.WAREHOUSE and tbd.ITEM=tbe.ITEM
    union 
    select
        tbg.WAREHOUSE,
        tbg.ITEM,
        ifnull(tbf.QTY, 0) as qty1,
        ifnull(tbg.QTY, 0) as qty3
    from G1102A as tbf right outer join G1102C as tbg on tbf.WAREHOUSE=tbg.WAREHOUSE and tbf.ITEM=tbg.ITEM) as tbh
on tbc.WAREHOUSE=tbh.WAREHOUSE and tbc.ITEM=tbh.ITEM
;