mysql - 您如何选择属于同一组的具有最低值的两行？

Question

我目前正在使用以下查询来选择两个可以以最慢速度到达目的地的“ worker ”，考虑到他们都在能够并且需要在指定时间到达目的地时离开。

SELECT worker_id, department, [ complicated equation using location, destination, departure and arrival time] AS mph
FROM workers
HAVING mph < 500
ORDER BY mph
LIMIT 2

这对于获得绝对最好的两个非常有效。但我现在需要做的是，确保所选 worker 来自同一 部门。假设 John 和 Sal 来自 department A，Tim 和 Rita 来自 department B。John 的 mph 是 20 而 Sal 是 60。Tim 的 mph 是 35，Rita 的是 50。由于 Tim 和 Rita 的最高 mph 较低 (50)，因此应该选择他们。

编辑

我应该提到，如果一个部门只有一名工作人员可以使速度低于 500 英里/小时，则 SELECT 应该从有两个工作人员的部门返回用户。如果没有两个部门，则选择最好的只有一个。

Answer 1

选择 mph 最高的工作人员并按该部门过滤您的查询:

SELECT w.worker_id, w.department, [ complicated equation using location, destination, departure and arrival time] AS mph
FROM workers w
CROSS JOIN (

    SELECT department, [ complicated equation using location, destination, departure and arrival time] AS mph1
    FROM workers
    HAVING mph1 < 500
    ORDER BY mph1
    LIMIT 1

) w2
WHERE w.department = w2.department
HAVING mph < 500
ORDER BY mph
LIMIT 2

更新

如果你不想只选择一个 worker ，也加入第二个 worker :

SELECT w.worker_id, w.department, [ complicated equation using location, destination, departure and arrival time] AS mph
FROM workers w
CROSS JOIN (

    SELECT department, 
        [ complicated equation using location, destination, departure and arrival time (by workers) ] AS mph1,
        [ complicated equation using location, destination, departure and arrival time (by w3) ] AS mph3
    FROM workers
    JOIN workers w3 ON w3.worker_id <> workers.worker_id
        AND w3.department = workers.department
    HAVING mph1 < 500 
        AND mph3 < 500
    ORDER BY mph1
    LIMIT 1

) w2
WHERE w.department = w2.department
HAVING mph < 500
ORDER BY mph
LIMIT 2