第一个case when,ifnull

最新推荐文章于 2022-12-20 18:19:30 发布

最新推荐文章于 2022-12-20 18:19:30 发布 · 83 阅读

本文详细介绍了如何使用SQL创建视图，包括视图的创建语法、基本操作以及多个视图之间的复杂组合与应用，旨在帮助数据库用户更高效地管理和查询数据。

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