mysql - 获取最小值、最大值和选定值

Question

我正在尝试从表中选择最小、最大和选定的行:

SELECT a.staff_id, u.username, a.date, a.role, a.global_scoring 
FROM quartz_quarter2 AS a 
RIGHT JOIN(SELECT staff_id, username FROM users)u ON a.staff_id = u.staff_id,
(SELECT MIN(global_scoring) AS mini, MAX(global_scoring) AS maxi FROM quartz_quarter2 
 WHERE date = '2019' AND role = 'Checker') AS m
WHERE m.maxi = a.global_scoring OR m.mini = a.global_scoring OR a.staff_id = '161'
AND date = '2019' AND role = 'Checker' GROUP BY global_scoring ORDER BY global_scoring;

在某些情况下它可以工作，但在某些情况下它会给我奇怪的输出，例如:

| staff_id | username | date |      role     | global_scoring |
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
|    100   |    Adam  | 2017 | Senior Artist |      1.99      |
|    161   |    John  | 2019 |     Checker   |      3.05      |
|    221   |    Anna  | 2019 |     Checker   |      3.84      |

它选择了错误的日期和角色。这可能是什么问题？有没有更好的方法在一个查询中选择最小值、最大值和选定的 id？

Answer 1

您似乎对逻辑运算符顺序有问题。把这个表达式括号内为 m.maxi = a.global_scoring OR m.mini = a.global_scoring OR a.staff_id = '161'。

所以最终的位置将是: WHERE (m.maxi = a.global_scoring OR m.mini = a.global_scoring OR a.staff_id = '161') AND date = '2019' AND role = 'Checker' GROUP BY global_scoring ORDER BY global_scoring;