按最快的响应时间对 MySQL 查询进行排序

Question

我有一个SQL查询：

SELECT number, ResponseTime, TicketCreateTime, 
round(time_to_sec(timediff(ResponseTime, TicketCreateTime))/60,2) AS FRMins
FROM (SELECT TE.id, T.number, T.ticket_id, TE.thread_id, TE.pid, T.created AS TicketCreateTime, TE.created AS ResponseTime, TE.type, TE.staff_id 
FROM ost_ticket T INNER JOIN
ost_thread_entry TE
ON T.ticket_id = TE.thread_id
WHERE TE.type = 'N' OR
TE.type = 'R'  AND TE.id IN (SELECT min(id) FROM ost_thread_entry WHERE type = 'N' OR type = 'R' GROUP BY thread_id)
) AS FTRT_tbl; 

number responseTime         TicketCreateTime  FRMins   
120985 2019-09-02 14:28:00  2019-09-02 14:10:00  18.0   
120985 2019-09-02 14:32:00  2019-09-02 14:10:00  22.0  
123490 2019-11-03 16:18:00  2019-11-03 16:17:00   1.0     
123490 2019-11-03 17:18:00  2019-11-03 16:17:00  61.0    

但我的目标是只有一个数字，它应该是FRMin最低的这个。我尝试了许多不同的方法，但我没有做对。

它应该看起来像这个

number responseTime         TicketCreateTime  FRMins  
120985 2019-09-02 14:28:00  2019-09-02 14:10:00  18.0   
123490 2019-11-03 16:18:00  2019-11-03 16:17:00   1.0   

Answer 1

如果你只想要一行，这能做你想要的吗？

select t.*
from ost_ticket t
order by t.FRMins
limit 1;

Answer 2

您可以使用WHERE EXISTS (SELECT ... GROUP BY ... HAVING)子查询来获取具有最低FRMins的不同number：

架构 (MySQL v5.7(

CREATE TABLE test (
`number` INTEGER,
`responseTime` DATETIME,
`TicketCreateTime` DATETIME,
`FRMins` INTEGER
);
INSERT INTO test
(`number`, `responseTime`, `TicketCreateTime`, `FRMins`)
VALUES
(120985, '2019-09-02 14:28:00', '2019-09-02 14:10:00', 18.0),
(120985, '2019-09-02 14:32:00', '2019-09-02 14:10:00', 22.0),
(123490, '2019-11-03 16:18:00', '2019-11-03 16:17:00', 1.0),
(123490, '2019-11-03 17:18:00', '2019-11-03 16:17:00', 61.0);

---

查询 #1

SELECT *
FROM test
WHERE EXISTS
(
SELECT 1
FROM test t
GROUP BY t.number
HAVING FRMins = MIN(t.FRMins)
);

输出：

| number | responseTime        | TicketCreateTime    | FRMins |
| ------ | ------------------- | ------------------- | ------ |
| 120985 | 2019-09-02 14:28:00 | 2019-09-02 14:10:00 | 18     |
| 123490 | 2019-11-03 16:18:00 | 2019-11-03 16:17:00 | 1      |

---

在DB Fiddle上查看