MySQL 查询基于多个列值选择顶级相机选项

Question

好的，所以我正在尝试从下面的表格设置中获得一些关于如何确定我拥有的最佳相机选择的想法。这不是一个真实的场景，我只是想找出最好的方法或一些方法来做这件事。

因此相机表具有唯一的条目 id(INT)、name(VARCHAR)、reviewRank(INT)、price(INT) 和 failRate(INT)。以下是 TABLE 设置和插入代码的示例:

CREATE TABLE CAMERA(
id INTEGER,
name VARCHAR(30),
reviewRank INT,
price INT,
failRate INT,
PRIMARY KEY(id))ENGINE=INNODB;

INSERT INTO CAMERA VALUES(1,'Camera 1',5,100,1);
INSERT INTO CAMERA VALUES(2,'Camera 2',4,300,7);
INSERT INTO CAMERA VALUES(3,'Camera 3',1,10,99);
INSERT INTO CAMERA VALUES(4,'Camera 4',1,10,6);

标准是reviewRank越高越好，价格越低越好，failRate越低越好。如果多个摄像机共享其中一列的最佳值，那么我们选择在其他两列中具有最佳值的那个，或者如果多个摄像机在多个列中具有相同的值，例如 CAMERA(3,'Camera 3',1 ,10,99) 和 CAMERA(4,'Camera 4',1,10,6) 然后最后一列决定结果。

基本上，任何在三列中具有最佳值(value)的相机都会自动成为可供选择的顶级相机列表的候选者。但是，如果多个摄像机对同一列具有最佳值，那么我们需要通过查看其他列的值来确定其中哪个是最佳值。

因此使用查询上方的数据库状态将返回相机 (1,'Camera 1',5,100,1) 和 (4,'Camera 4',1,10,6) 因为相机 id 1 具有最高的 reviewRank 和最低故障率，相机 ID 4 和相机 ID 3 价格最低，但相机 ID 4 的故障率低于相机 ID 3。

所以基本上我想找到一种方法来找到最佳选择，其中使用 MySQL 进行多列比较，如果可能的话最好使用单个查询。

我最初的想法是这样的

SELECT DISTINCT C.* 
FROM CAMERA C
WHERE C.reviewRank>=ALL(SELECT reviewRank FROM CAMERA)
OR    C.price<=ALL(SELECT price FROM CAMERA)
OR    C.failRate<=ALL(SELECT failRate FROM CAMERA);

但这显然是错误的，因为它没有涵盖所有可能的排列。在做了很多寻找解决这个问题的方法之后，我仍然不知道如何去做。

Answer 1

我相信 ORDER BY条款完成你正在寻找的东西:

SELECT topC.Id, topC.Name, topC.ReviewRank, topC.Price, topC.FailRate
FROM Camera topC JOIN (
    (SELECT c.ReviewRank, c.Price, c.FailRate
    FROM Camera c
    ORDER BY 
        c.ReviewRank DESC,
        c.Price,
        c.FailRate
    LIMIT 1)
    UNION 
    (SELECT c.ReviewRank, c.Price, c.FailRate
    FROM Camera c
    ORDER BY 
        c.Price,
        c.FailRate,
        c.ReviewRank DESC
    LIMIT 1)
    UNION 
    (SELECT c.ReviewRank, c.Price, c.FailRate
    FROM Camera c
    ORDER BY 
        c.FailRate,
        c.Price,
        c.ReviewRank DESC
    LIMIT 1)
) values 
    ON values.ReviewRank = topC.ReviewRank
    AND values.Price = topC.price
    AND values.FailRate = topC.FailRate