mysql - 如何在过程中通过两个查询的结果进行计算

Question

我有一个名为 ProfitCalculation 的程序。它需要三个参数..过程主体如下:

delimiter //

CREATE PROCEDURE ProfitCalculation
     (
        IN   instrument                      INT(20)    , 
        OUT  buy                             float(10,3)  ,
        OUT  sell                            float(10,3)    ,
        OUT  profit                          float(10,3)   
     )
BEGIN 

    SELECT SUM(FldLastTradeQuantity*FldPrice) as total_buy
    INTO   buy                              
    FROM   TblOrders
    WHERE  FldInstrumentID = instrument AND FldBuySell = 'b' AND FldLastTradePrice != 0 AND FldLastTradeQuantity != 0 group by FldInstrumentID;


    SELECT SUM(FldLastTradeQuantity*FldPrice) as total_buy
    INTO   sell                      
    FROM   TblOrders
    WHERE  FldInstrumentID = instrument AND FldBuySell = 's' AND FldLastTradePrice != 0 AND FldLastTradeQuantity != 0 group by FldInstrumentID; 

     profit = sell - buy;  

END
delimiter ;

现在我想计算利润，但它只返回1。请给我一个将返回实际结果的解决方案。

Answer 1

此过程可以简化为单个 SQL 查询，这可能会帮助您进行调查。

SELECT buy, sell, (sell - buy) profit FROM (
    SELECT 
        SUM(CASE FldBuySell WHEN 'b' THEN FldLastTradeQuantity*FldPrice END) buy,
        SUM(CASE FldBuySell WHEN 's' THEN FldLastTradeQuantity*FldPrice END) sell
    FROM TblOrders
    WHERE FldInstrumentID = instrument AND FldLastTradePrice != 0 
      AND FldLastTradeQuantity != 0
) a;