相关子查询中的MySQL LIMIT [英] MySQL LIMIT in a Correllated Subquery

问题描述

我有一个相关的子查询，该子查询将返回数量列表，但我需要的数量最高，也只有最高的数量.因此，我试图引入一个以LIMIT为1的顺序来实现此目的，但是MySQL抛出一个错误，指出它尚不支持子查询中的限制.关于如何解决这个问题有什么想法吗?

I have a correlated subquery that will return a list of quantities, but I need the highest quantity, and only the highest. So I tried to introduce an order by and a LIMIT of 1 to achieve this, but MySQL throws an error stating it doesn't yet support limits in subqueries. Any thoughts on how to work around this?

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)

推荐答案

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

This is a variation of the greatest-n-per-group problem that comes up frequently.

您想要单行形式FinishTierPrice(称为p1)，匹配FinishOption并具有最大数量，但仍小于或等于ProductOptionTier的数量.

You want the single row form FinishTierPrice (call it p1), matching the FinishOption and with the greatest Qty, but still less than or equal to the Qty of the ProductOptionTier.

执行此操作的一种方法是尝试匹配来自FinishTierPrice的第二行(p2)，该行将具有相同的FinishOption和更大的数量.如果不存在这样的行(使用外部联接并测试其为NULL)，则p1找到的行最大.

One way to do this is to try to match a second row (p2) from FinishTierPrice that would have the same FinishOption and a greater Qty. If no such row exists (use an outer join and test that it's NULL), then the row found by p1 is the greatest.

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

这篇关于相关子查询中的MySQL LIMIT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！