在mysql中选择最大日期差异

Question

我有这样的疑问

select ps.trial_id, ps.submit_date, pa.approved_date, TIMESTAMPDIFF(DAY,ps.submit_date, 
pa.approved_date) as dias_p_aprovar from picolo_submit ps join picolo_approved pa on   
ps.trial_id = pa.trial_id where ps.submit_date < pa.approved_date order by ps.trial_id;

这是一个结果的样本：

|    12418 | 2022-01-27 20:09:58 | 2022-01-28 15:54:37 |              0 |
|    12418 | 2022-01-28 15:52:25 | 2022-01-28 15:54:37 |              0 |
|    12419 | 2022-01-20 22:29:41 | 2022-01-27 16:43:23 |              6 |
|    12419 | 2022-01-25 15:07:27 | 2022-01-27 16:43:23 |              2 |
|    12419 | 2022-01-27 14:45:46 | 2022-01-27 16:43:23 |              0 |
|    12419 | 2022-01-27 15:42:09 | 2022-01-27 16:43:23 |              0 |
|    12439 | 2022-01-26 16:41:24 | 2022-01-28 17:31:19 |              2 |
|    12439 | 2022-01-27 15:42:20 | 2022-01-28 17:31:19 |              1 |
|    12439 | 2022-01-27 18:16:11 | 2022-01-28 17:31:19 |              0 |
|    12439 | 2022-01-28 14:12:17 | 2022-01-28 17:31:19 |              0 |
|    12439 | 2022-01-28 17:15:16 | 2022-01-28 17:31:19 |              0 |
+----------+---------------------+---------------------+----------------+
6911 rows in set (0,06 sec)

我需要选择每个ps.trial_id最大的TIMESTAMPDIFF的结果。在ps.trial_id = 12439的情况下，我只需要第一行。

|    12439 | 2022-01-26 16:41:24 | 2022-01-28 17:31:19 |              2 |

有可能吗？

Answer 1

在MySQL 8+上，我们可以使用ROW_NUMBER：

WITH cte AS (
    SELECT ps.trial_id, ps.submit_date, pa.approved_date,
           TIMESTAMPDIFF(DAY, ps.submit_date, pa.approved_date) AS dias_p_aprovar,
           ROW_NUMBER() OVER (PARTITION BY ps.trial_id
                              ORDER BY TIMESTAMPDIFF(DAY, ps.submit_date, pa.approved_date) DESC) rn
    FROM picolo_submit ps
    INNER JOIN picolo_approved pa ON ps.trial_id = pa.trial_id
    WHERE ps.submit_date < pa.approved_date
)

SELECT trial_id, submit_date, approved_date, dias_p_aprovar
FROM cte
WHERE rn = 1
ORDER BY trial_id;