相关子查询中的 MySQL LIMIT

Question

我有一个相关子查询，它将返回数量列表，但我需要最高数量，而且只需要最高数量。因此，我尝试引入 order by 和 LIMIT 1 来实现此目的，但 MySQL 抛出一个错误，指出它还不支持子查询中的限制。关于如何解决这个问题有什么想法吗？

SELECT Product.Name, ProductOption.Name, a.Qty, a.Price, SheetSize.UpgradeCost,
        FinishType.Name, FinishOption.Name, FinishTierPrice.Qty, FinishTierPrice.Price
FROM `Product`
    JOIN `ProductOption`
        ON Product.idProduct = ProductOption.Product_idProduct
    JOIN `ProductOptionTier` AS a
        ON a.ProductOption_idProductOption = ProductOption.idProductOption
    JOIN `PaperSize`
        ON PaperSize.idPaperSize = ProductOption.PaperSize_idPaperSize
    JOIN `SheetSize`
        ON SheetSize.PaperSize_idPaperSize = PaperSize.idPaperSize
    JOIN `FinishOption`
        ON FinishOption.Product_idProduct = Product.idProduct
    JOIN `FinishType`
        ON FinishType.idFinishType = FinishOption.Finishtype_idFinishType
    JOIN `FinishTierPrice`
        ON FinishTierPrice.FinishOption_idFinishOption = FinishOption.idFinishOption
WHERE Product.idProduct = 1
    AND FinishTierPrice.idFinishTierPrice IN (SELECT FinishTierPrice.idFinishTierPrice
                                              FROM `FinishTierPrice` 
                                              WHERE FinishTierPrice.Qty <= a.Qty
                                              ORDER BY a.Qty DESC
                                              LIMIT 1)

Answer 1

这是经常出现的greatest-n-per-group问题的变体。

您需要单行形式的 FinishTierPrice（称为 p1），与 FinishOption 匹配并具有最大数量，但仍小于或等于 ProductOptionTier 的数量。

实现此目的的一种方法是尝试匹配 FinishTierPrice 中具有相同 FinishOption 和更大数量的第二行 (p2)。如果不存在这样的行（使用外连接并测试它是否为 NULL），则 p1 找到的行是最大的。

SELECT Product.Name, ProductOption.Name, a.Qty, a.Price, SheetSize.UpgradeCost,
        FinishType.Name, FinishOption.Name, FinishTierPrice.Qty, FinishTierPrice.Price
FROM `Product`
    JOIN `ProductOption`
        ON Product.idProduct = ProductOption.Product_idProduct
    JOIN `ProductOptionTier` AS a
        ON a.ProductOption_idProductOption = ProductOption.idProductOption
    JOIN `PaperSize`
        ON PaperSize.idPaperSize = ProductOption.PaperSize_idPaperSize
    JOIN `SheetSize`
        ON SheetSize.PaperSize_idPaperSize = PaperSize.idPaperSize
    JOIN `FinishOption`
        ON FinishOption.Product_idProduct = Product.idProduct
    JOIN `FinishType`
        ON FinishType.idFinishType = FinishOption.Finishtype_idFinishType
    JOIN `FinishTierPrice` AS p1
        ON p1.FinishOption_idFinishOption = FinishOption.idFinishOption
        AND p1.Qty <= a.Qty
    LEFT OUTER JOIN `FinishTierPrice` AS p2
        ON p2.FinishOption_idFinishOption = FinishOption.idFinishOption
        AND p2.Qty <= a.Qty AND (p2.Qty > p1.Qty OR p2.Qty = p1.Qty 
            AND p2.idFinishTierPrice > p1.idFinishTierPrice)
WHERE Product.idProduct = 1
    AND p2.idFinishTierPrice IS NULL