求一条SQL语句，高手来。有两个表stock、sale，stock里保存的是产品总数，sale里保存的是销售的数据。

发布网友发布时间：2022-10-12 18:44

共2个回答

热心网友时间：2023-10-23 23:54

oracle的话是:
select stock.型号,(stock.数量-nvl((select sale.数量 from sale where sale.型号=stock.型号),0)) 库存 from stock ;
sql server没有nvl函数则改成
select stock.sno,(stock.num-isnull((select sale.num from sale where sale.sno=stock.sno),0)) 库存 from stock ;
mysql的话又改成:
select stock.sno,(stock.num-ifnull((select sale.num from sale where sale.sno=stock.sno),0)) 库存 from stock ;

热心网友时间：2023-10-23 23:55

销售数据应该汇总一下吧
SELECT A.型号,A.数量-ISNULL(B.数量,0) AS 数量 FROM stock A LEFT JOIN
(SELECT 型号,SUM(数量) AS 数量 FROM sale GROUP BY 型号 ) B ON B.型号=A.型号