MySQL SELECT 对一组数字中的一个数字进行排名

Question

我有来自 SELECT 查询的数据行，其中包含一些价格(在本例中假设为 3 个)。一个是我们的价格，一个是竞争对手 1 的价格，一个是竞争对手 2 的价格。我想添加一列，列出我们的价格与其他两个价格相比的排名；如果我们的价格是最低的，它会吐出数字 1；如果是最高的，它会吐出数字。

类似这样的事情:

Make |  Model  |  OurPrice | Comp1Price  | Comp2Price | Rank |  OutOf
MFG1    MODEL1     350         100           500         2        3
MFG1    MODEL2     50          100           100         1        3
MFG2    MODEL1     100         NULL          50          2        2
MFG2    MODEL2     9999        500           NULL        2        2

-有时竞争对手的价格将为 NULL，如上所示，我相信这就是我的问题所在。我尝试过一种CASE，它仅适用于一个竞争对手，但当我添加 AND 语句时，它会将排名全部显示为NULL。有没有更好的方法通过 MySQL 查询来做到这一点？

SELECT
      MT.MAKE as Make,
      MT.MODEL as Model,
      MT.PRICE as OurPrice,
      CT1.PRICE as Comp1Price,
      CT2.PRICE as Comp2Price,
      CASE
         WHEN MT.PRICE < CT1.PRICE AND MT.PRICE < CT2.PRICE
         THEN 1 END AS Rank
      (CT1.PRICE IS NOT NULL) + (CT2.PRICE IS NOT NULL) + 1 as OutOf
  FROM mytable MT

LEFT JOIN competitor1table as CT1 ON CT1.MODEL = MT.MODEL
LEFT JOIN competitor2table as CT2 ON CT2.MODEL = MT.MODEL

ORDER BY CLASS

Answer 1

尚未测试，但您可以尝试:

SELECT
    a.MAKE AS Make,
    a.MODEL AS Model,
    a.PRICE AS OurPrice
    MAX(CASE WHEN a.compnum = 1 THEN pricelist END) AS Comp1Price,
    MAX(CASE WHEN a.compnum = 2 THEN pricelist END) AS Comp2Price,
    FIND_IN_SET(a.PRICE, GROUP_CONCAT(a.pricelist ORDER BY a.pricelist)) AS Rank,
    COUNT(a.pricelist) AS OutOf
FROM
(
    SELECT MAKE, MODEL, PRICE, PRICE AS pricelist, 0 AS compnum
    FROM mytable

    UNION ALL

    SELECT a.MAKE, a.MODEL, a.PRICE, CT1.PRICE, 1
    FROM mytable a
    LEFT JOIN competitor1table CT1 ON a.MODEL = CT1.MODEL

    UNION ALL

    SELECT a.MAKE, a.MODEL, a.PRICE, CT2.PRICE, 2
    FROM mytable a
    LEFT JOIN competitor2table CT2 ON a.MODEL = CT2.MODEL
) a
GROUP BY
    a.MAKE, a.MODEL