mysql - MySQL中如何获取每种类型中最早的一个

Question

对于以下数据:

mysql> select * from policy_redeem_window;
+----+---------+-----------+----------+-------+---------------------+---------+---------------------+
| id | user_id | policy_id | delta_id | value | start_date          | state   | created             |
+----+---------+-----------+----------+-------+---------------------+---------+---------------------+
|  1 |       0 | policy1   | delta1   |  1.00 | 2019-12-11 14:22:21 | PENDING | 2019-12-11 14:22:21 |
|  2 |       0 | policy1   | delta2   |  1.00 | 2019-12-12 14:22:33 | PENDING | 2019-12-11 14:22:33 |
|  3 |       0 | policy2   | delta3   |  1.00 | 2019-12-11 14:22:45 | PENDING | 2019-12-11 14:22:45 |
|  4 |       0 | policy2   | delta4   |  1.00 | 2019-12-12 14:23:08 | ACTIVE  | 2019-12-11 14:23:08 |
|  6 |       0 | policy2   | delta5   |  1.00 | 2019-12-11 14:23:37 | ACTIVE  | 2019-12-11 14:23:37 |
+----+---------+-----------+----------+-------+---------------------+---------+---------------------+
5 rows in set (0.00 sec)

我试图为每个 policy_id 获取一行，其中每一行都是具有最早 start_date 的行:

我尝试了以下查询:

select * 
from policy_redeem_window 
where user_id = 0 
and state in ('ACTIVE', 'PENDING') 
group by policy_id 
order by start_date desc;

但这给了我以下错误:

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'admiral.policy_redeem_window.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

我哪里出错了？

Answer 1

一种常见的解决方法是加入聚合，这允许您获取每个组的完整行:

SELECT t.*
FROM (
    SELECT policy_id, MIN(start_date) AS first_date
    FROM policy_redeem_window
    WHERE user_id = 0
    AND state IN ('ACTIVE', 'PENDING')
    GROUP BY policy_id
) AS g
JOIN policy_redeem_window AS t ON g.policy_id = t.policy_id AND g.first_date = t.start_date