我从SELECT查询中获得几行数据(有三个例子)。一个是我们的价格,一个是竞争对手的价格,一个是竞争对手的价格。我想添加一个与其他两个价格相比排列出我们价格级别的列;如果我们的价格最低,它会吐出数字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