相关子查询中的MySQL LIMIT

本文介绍了相关子查询中的MySQL LIMIT的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个相关的子查询，该子查询将返回数量列表，但我需要的数量最高，也只有最高的数量.因此，我试图引入一个以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