CREATE VIEW viewRece AS SELECT id,number,SUM(qty) AS qtyRece FROM receving GROUP BY number CREATE VIEW viewShip AS SELECT id,number,SUM(qty) AS qtyShip FROM ship GROUP BY number; CREATE VIEW viewInv AS SELECT R.number,qtyRece-qtyShip FROM viewRece R LEFT JOIN viewShip S ON R.number=S.number CREATE VIEW view_Inv3 AS SELECT R.number,(R.qtyRece-(CASE WHEN qtyShip>0 THEN qtyShip ELSE 0 END)) AS qtyInv FROM viewRece R LEFT JOIN viewShip S ON R.number=S.number CREATE VIEW view_Inv2 AS SELECT R.number,R.qtyRece-ISNULL(S.qtyShip,0) AS qtyInv FROM viewRece R LEFT JOIN viewShip S ON R.number=S.number
CREATE VIEW viewRece AS SELECT id,number,SUM(qty) AS qtyRece FROM receving GROUP BY numberCREATE VIEW viewShip AS SELECT id,number,SUM(qty) AS qtyShip FROM ship GROUP BY number;CREATE VIEW viewInv AS SELECT R.number,qtyRece-qtyShip FROM viewRece R LEFT JOIN viewShip S ON R.number=S.numberCREATE VIEW view_Inv AS SELECT R.number,(CASE WHEN qtyShip>0 THEN qtyShip ELSE 0 END) AS qtyInv FROM viewRece R LEFT JOIN viewShip S ON R.number=S.number
第一个case when,ifnull
最新推荐文章于 2020-04-01 15:12:05 发布