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;