php - 在复杂的 MySQL 查询中获取整行 MIN 值

Question

我发现了几篇讨论使用特定 MIN() 值获取表的整行的帖子，但我不知道如何将其添加到我有些复杂的查询中。

我使用它来创建一个 View ，以便我有一个项目表及其最低的事件成本。其他流程已就位以使用此包含 4 列的 View :项目编号 - PL_IPRecNbr成本 - 成本价格表说明 - PH_VndrDocumentID价目表记录# - PH_RecNbr

这是我的 MySQL 查询:

SELECT `pl`.`PL_IPRecNbr` AS `PL_IPRecNbr`,
IF (min(`pl`.`PL_ATRLandedCostEach`) = 0, min(`pl`.`PL_ATRCostEach`), 
min(`pl`.`PL_ATRLandedCostEach`)) AS `Cost`,
`ph`.`PH_VndrDocumentID` AS `PH_VndrDocumentID`,
`ph`.`PH_RecNbr` AS `PH_RecNbr`
FROM `pl`
JOIN `ph` ON 
`pl`.`PL_PHRecNbr` = `ph`.`PH_RecNbr`
WHERE isnull(`ph`.`PH_ReplacedByPH_RecNbr`)
AND `ph`.`PH_ExcludeSheetFromAPS` = 'N'
AND (`pl`.`PL_PurchaseQty` > `pl`.`PL_SoldQty` OR `pl`.`PL_PurchaseQty` = 0)
AND cast(now() AS date) BETWEEN `ph`.`PH_PricesStartDate`
AND `ph`.`PH_PricesEndDate`
GROUP BY `pl`.`PL_IPRecNbr`

问题是我获得的“Cost”值是正确的，但我获得的“PH_VndrDocumentID”和“PH_RecNbr”值不正确。我需要它们是与成本位于同一行的值。

我尝试了很多尝试并搜索了很多答案，但无法思考如何实现我需要的结果。

引用表说明:

PH is a table of "Price Sheet Headers" - Also called just Price Sheets. - They are "Active when they fall into a start and end date range of today - There are multiple price sheets active for each item, I need the lowest one. - There are sometimes limits on how many items are available thats why we check to see if the purchased qty is greater than the Sold Qty.  - If there is no limit on items the PurchaseQty shows a 0.
PL is a tables of Prices belonging to each "PH". - Each line is a Price for an item  - The price is either in one of two columns either PL_ATRLandedCostEach or PL_ATRCostEach, if Landed is 0 then its in the other column, that why the IF.

正如您所看到的，我正在尝试使用仅根据日期和其他因素查找有效价格表的标准来确定最低价格和相应的价格表 (PHRecNbr)。

任何讨论或指示都会有帮助。预先感谢您!

Answer 1

您的 PL 的 ph.PH_RecNbr 是否始终与 PL_PHRecNbr 匹配？如果是这样，您可以终止连接并强制使用正确的 PH_VndrDocumentID PH_RecNbr(消除由 JOIN 引起的任何歧义):

SELECT
  pl.PL_IPRecNbr           AS PL_IPRecNbr,
  IF (
    min( `pl`.`PL_ATRLandedCostEach` ) = 0,
    min( `pl`.`PL_ATRCostEach` ),
    min( `pl`.`PL_ATRLandedCostEach` )
  )                        AS `Cost`,
  `ph`.`PH_VndrDocumentID` AS `PH_VndrDocumentID`,
  `ph`.`PH_RecNbr`         AS `PH_RecNbr`
FROM
  pl,
  ph
WHERE
  pl.PL_PHRecNbr = ph.PH_RecNbr     AND 
  ph.PH_ReplacedByPH_RecNbr IS NULL AND
  ph.PH_ExcludeSheetFromAPS = 'N'   AND
  pl.PL_PurchaseQty > pl.PL_SoldQty AND
  pl.PL_PurchaseQty = 0             AND 
  cast(now() AS date) BETWEEN ph.PH_PricesStartDate AND ph.PH_PricesEndDate
GROUP BY
  pl.PL_IPRecNbr

这将强制返回的每一行基于单个 pl.PL_IPRecNbr 项(假设每行只有其中一个)。