mysql - 在查询中重用计算

Question

我想在我的数据库中创建一个包含少量逻辑的 View 。这是为了检查产品可用性。我创建了一个简化的模式作为 a SQL Fiddle .

但是，我需要在多个列中重复使用一个值。这是我想做的:

SELECT
    product.id AS id,
    SUM(IFNULL(purchase_product.amount,0)) AS amountSold,
    IFNULL(product.amountAvailable-amountSold,~0) AS amountAvailable,
    amountAvailable>0 AS isAvailable,
FROM product
    LEFT JOIN purchase_product ON purchase_product.product_id = product.id
    LEFT JOIN purchase ON purchase.id = purchase_product.purchase_id AND purchase.completed = TRUE
GROUP BY product.id

但这失败了，因为我不能重复使用“amountSold”和“amountAvailable”。这让我可以选择是重复计算还是使用子查询(/加入 SELECT/使用更多 View )。

这是我在 Fiddle 中所能想到的最合理的折衷方案。 有没有更优雅的解决方案？

Answer 1

我非常喜欢您当前的解决方案，我认为它没有真正的问题，但是我认为这可能会稍微简单一些:

SELECT  Product.ID, 
        COALESCE(AmountSold, 0) AS AmountSold,
        GREATEST(COALESCE(AmountAvailable, ~0) - COALESCE(AmountSold, 0), 0) AS AmountAvailable,
        COALESCE(AmountAvailable, ~0) - COALESCE(AmountSold, 0) > 0 AS IsAvailable
FROM    Product
        LEFT JOIN 
        (   SELECT  Product_ID, SUM(Amount) AS AmountSold
            FROM    Purchase_Product
                    INNER JOIN Purchase
                        ON Purchase.ID = Purchase_ID
            WHERE   Completed = 1
            GROUP BY Product_ID
        ) sold
            ON Sold.Product_ID = Product.ID;

唯一真正的变化是我在子查询中删除了对产品的引用以减少表扫描次数，并且还稍微改变了计算逻辑。

最后我在其中添加了 GREATEST，只是为了解决可能存在的任何数据完整性问题，这样您就永远不会有负面产品可用。

您可以执行进一步的子查询以增加查询的可读性，但它几乎肯定不会提高性能。

SQL Fiddle显示略有改进的执行计划。