如何缩短此MySQL查询的时间？

Question

这里，那里，

有没有缩短这个MySQL查询的方法？它需要根据价格规则从名为RuleValue的列中从PriceRuleDetail表中抓取两个不同的行，但是使用别名，在同一行中返回它们。它在select语句中使用子查询，我认为这是正确的，但可能有更简单的方法？

下面的代码运行正常，我对此相当满意，但我想知道是否有方法可以让它更简短？

SELECT Stock.*, 
    (SELECT PriceRuleDetail.RuleValue
    FROM PriceRuleDetail
    WHERE PriceRuleDetail.Sku = Stock.Sku
    AND PriceRuleDetail.PriceRule = 'RG'
    AND PriceRuleDetail.Quantity = 1) as Price,
    (SELECT PriceRuleDetail.RuleValue
    FROM PriceRuleDetail
    WHERE PriceRuleDetail.Sku = Stock.Sku
    AND PriceRuleDetail.PriceRule = 'RRP'
    AND PriceRuleDetail.Quantity = 1) as WasPrice
FROM Stock, StockCategoryMemberList
WHERE StockCategoryMemberList.Sku = Stock.Sku
AND StockCategoryMemberList.CategoryCode = 'FIRE'

提前谢谢。

安迪

Answer 1

您可以使用左连接来实现这一点

 SELECT Stock.*, 
        PRD1.RuleValue as Price, 
        PRD2.RuleValue as WasPrice
FROM 
   Stock INNER JOIN
   StockCategoryMemberList ON StockCategoryMemberList.Sku = Stock.Sku LEFT JOIN 
   PriceRuleDetail PRD1 ON PRD1.Sku = Stock.Sku
                        AND PRD1.PriceRule = 'RG'
                        AND PRD1.Quantity = 1 LEFT JOIN
   PriceRuleDetail PRD2 ON PRD2.Sku = Stock.Sku
                        AND PRD2.PriceRule = 'RRP'
                        AND PRD2.Quantity = 1
WHERE StockCategoryMemberList.CategoryCode = 'FIRE'

Answer 2

使其成为一个带有连接的单一查询：

SELECT
   Stock.*, 
   PRD1.RuleValue as Price, 
   PRD2.RuleValue as WasPrice
FROM 
   Stock, 
   StockCategoryMemberList, 
   PriceRuleDetail PRD1, 
   PriceRuleDetail PRD2
WHERE
StockCategoryMemberList.Sku = Stock.Sku
AND StockCategoryMemberList.CategoryCode = 'FIRE'
AND PRD1.Sku = Stock.Sku
    AND PRD1.PriceRule = 'RG'
    AND PRD1.Quantity = 1
AND
    PRD2.Sku = Stock.Sku
    AND PRD2.PriceRule = 'RRP'
    AND PRD2.Quantity = 1

我做了与您相同的假设(对于一个股票记录，只有一个价格和一个WasPrice)。如果不是这样的话...查询将不会正常。也不是你的。

Answer 3

select s.*, prd1.rulevalue as price, prd2.rulevalue as wasprice
from
  stock s
  inner join stockcategorymemberlist scm
      on s.sku = scm.sku
  inner join priceruledetail prd1
      on prd1.sku = s.sku and prd1.pricerule='RG' and prd1.quantity=1
  inner join priceruledetail prd2
      on prd2.sku = s.sku and prd2.pricerule='RRP' and prd2.quantity=1
where
  s.categorycode='FIRE'

这也会更快，因为您不必为表中的每一行执行子选择。