Left Join 右表数据不唯一解决方法

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_32738723/article/details/81214415
CREATE OR REPLACE VIEW VI_DC_DYC_STOCK_RPT AS
SELECT r.CODE CODE,r.NAME NAME,r.UNIT UNIT,r.MODULE MODULE,r.DEPART DEPART,r.DEPART_NAME,r.QTY qty,r.SORTS_code,r.SORTS_name,s.QTY dc_qty,nvl(l.QTY,0) lock_qty,s.QTY-nvl(l.QTY,0) can_qty ,k.cost cost
FROM VI_DC_STOCK_RPT r
LEFT JOIN VI_DC_STOCK  s ON r.CODE = s.CODE AND r.DEPART = s.DEPART
LEFT JOIN VI_LOCK_STOCK l ON r.CODE = l.CODE AND r.DEPART = l.DEPART
--因为cost对应多行,所以做了一下group
left join (select code,depart,max(cost) cost from cur_stock group by code,depart) k on r.CODE = k.code and r.DEPART = k.depart;

 

展开阅读全文

没有更多推荐了,返回首页