SELECT
(
CASE
WHEN (
sd.SO_SalesOrderDetail_FK = '00000000-0000-0000-0000-000000000000'
OR sd.SO_SalesOrderDetail_FK IS NULL
) THEN
(
CASE ifnull(sd.BuyNum, 0)
WHEN 0 THEN
0
ELSE
sd.ReceivablePrice / sd.BuyNum
END
)
ELSE
(
CASE ifnull(sd2.BuyNum, 0)
WHEN 0 THEN
0
ELSE
(
sd.BuyNum * sd.FKRP_SalesUnitPrice * sd2.ReceivablePrice
) / (
sd2.BuyNum * sd2.FKRP_SalesUnitPrice
) / sd.BuyNum
END
)
END
) AS _result
FROM
SO_SalesOrder so
LEFT OUTER JOIN SO_SalesOrderDetail sd ON so.SO_SalesOrderOID = sd.SO_SalesOrder_FK
LEFT OUTER JOIN SO_SalesOrderDetail sd2 ON sd2.SO_SalesOrderDetailOID = sd.SO_SalesOrderDetail_FK
WHERE
sd.BI_Product_Item_FK != '00000000-0000-0000-0000-000000000000'
AND sd.BI_Product_Item_FK IS NOT NULL
AND sd.SO_SalesOrderDetailOID = '000779c7-0000-0000-0000-0000bf087b0e'
SELECT ( CASE WHEN ( sd.SO_SalesOrderDetail_FK = '00000000-0000-0000-0000-000000000000' OR sd.SO_SalesOrderDetail_FK IS NULL ) THEN CASE WHEN ifnull(sd.BuyNum, 0) = 0 THEN 0 ELSE sd.ReceivablePrice / sd.BuyNum END ELSE CASE WHEN ifnull(sd2.BuyNum, 0) = 0 THEN 0 ELSE ( sd.BuyNum * sd.FKRP_SalesUnitPrice * sd2.ReceivablePrice ) / ( sd2.BuyNum * sd2.FKRP_SalesUnitPrice ) / sd.BuyNum END
ELSE 0
END ) AS _result FROM SO_SalesOrder so LEFT OUTER JOIN SO_SalesOrderDetail sd ON so.SO_SalesOrderOID = sd.SO_SalesOrder_FK LEFT OUTER JOIN SO_SalesOrderDetail sd2 ON sd2.SO_SalesOrderDetailOID = sd.SO_SalesOrderDetail_FK WHERE sd.BI_Product_Item_FK != '00000000-0000-0000-0000-000000000000' AND sd.BI_Product_Item_FK IS NOT NULL AND sd.SO_SalesOrderDetailOID = '000779c7-0000-0000-0000-0000bf087b0e';
这sql 是对的。。在这段 sql 之前还有个select ,那错了。。擦。。。
参考http://blog.csdn.net/codepen/article/details/46688671