inner join 和 union all 做的汇总区别

in 编程
关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9

inner join

CREATE OR REPLACE VIEW M_INVENT_BARCODE_DIFF AS
SELECT "INV_PART_NO","INV_ONHAND","INV_LOCATION","PART_NO","BAR_ONHAND","BAR_LOCATION"
	FROM (			SELECT m.part_no AS inv_part_no, ROUND(SUM(m.qty_onhand), 13)  AS inv_onhand  , m.location_no AS inv_location FROM inventory_part_in_stock_tab m GROUP BY m.part_no ,m.location_no ) inv ,
      (	SELECT n.part_no , ROUND(SUM(n.qty),13) bar_onhand , n.location_no AS bar_location  FROM m_location_no_barcode n GROUP BY n.part_no , n.location_no ) bar
	WHERE inv.inv_part_no = bar.part_no
	AND   inv.inv_location = bar.bar_location
	AND   nvl(inv.inv_onhand,0) != nvl(bar.bar_onhand ,0)
WITH   read only;

但是 左右空值不匹配的没有显示出来

left join

CREATE OR REPLACE VIEW M_INVENT_BARCODE_DIFF AS
SELECT part_no,
         location_no                 location_no, 
         SUM(NVL(INV_ONHAND,0))      INV_ONHAND,
         SUM(nvl(bar_onhand,0))      bar_onhand
  FROM (SELECT M.PART_NO AS                    PART_NO,
               M.LOCATION_NO AS                location_no,
               ROUND(SUM(M.QTY_ONHAND), 13) AS INV_ONHAND,
               0                               bar_onhand 
          FROM INVENTORY_PART_IN_STOCK_TAB M
         GROUP BY M.PART_NO, M.LOCATION_NO 
         UNION ALL 
         SELECT N.PART_NO              PART_NO,
               N.LOCATION_NO AS        location_no,
                0                      inv_onhand, 
               ROUND(SUM(N.QTY), 13)   BAR_ONHAND
          FROM M_LOCATION_NO_BARCODE N
         GROUP BY N.PART_NO, N.LOCATION_NO 
       )
  GROUP BY part_no, location_no 
  HAVING SUM(NVL(INV_ONHAND,0)) != SUM(nvl(bar_onhand,0)) 
WITH   read only;

 

关注公众号【好便宜】( ID:haopianyi222 ),领红包啦~
阿里云,国内最大的云服务商,注册就送数千元优惠券:https://t.cn/AiQe5A0g
腾讯云,良心云,价格优惠: https://t.cn/AieHwwKl
搬瓦工,CN2 GIA 优质线路,搭梯子、海外建站推荐: https://t.cn/AieHwfX9
扫一扫关注公众号添加购物返利助手,领红包
Comments are closed.

推荐使用阿里云服务器

超多优惠券

服务器最低一折,一年不到100!

朕已阅去看看