如何在MySQL中获得每种类型的最早信息

2024-05-21 • 问答

对于以下数据：

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;

但这给了我以下错误：

SELECT列表的表达式＃1不在GROUP BY子句中，并且包含未聚合的列'admiral.policy_redeem_window.id'，该列在功能上不依赖于GROUP BY子句中的列；这与sql_mode = only_full_group_by
不兼容

我在哪里错了？

一种常见的解决方法是通过聚合进行联接，这使您可以为每个组获取完整的行：

numpy.testing.assert_array_equal([[0,1,0],[1,1],[0,0]],array_example_0)

使用NOT EXISTS：

select p.* from policy_redeem_window p
where p.user_id = 0 
and p.state in ('ACTIVE','PENDING')
and not exists (
  select 1 from policy_redeem_window 
  where policy_id = p.policy_id and user_id = p.user_id and state in ('ACTIVE','PENDING') 
  and start_date < p.start_date 
);

请参见demo。
对于带有ROW_NUMBER()的MySql 8.0 +：

select p.id,p.user_id,p.policy_id,p.delta_id,p.value,p.start_date,p.state,p.created
from (
  select *,row_number() over (partition by policy_id order by start_date) rn
  from policy_redeem_window
  where user_id = 0 and state in ('ACTIVE','PENDING')
) p
where p.rn = 1;

请参见demo。
结果：

| id  | user_id | policy_id | delta_id | value | start_date          | state   | created             |
| --- | ------- | --------- | -------- | ----- | ------------------- | ------- | ------------------- |
| 1   | 0       | policy1   | delta1   | 1     | 2019-12-11 14:22:21 | PENDING | 2019-12-11 14:22:21 |
| 3   | 0       | policy2   | delta3   | 1     | 2019-12-11 14:22:45 | PENDING | 2019-12-11 14:22:45 |

您需要对不在group-by子句中的每一列都放置一个聚合函数。也就是说，因为您需要为多行结果集选择一个标量值才能返回。

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

如何在MySQL中获得每种类型的最早信息

clshz2009 回答：如何在MySQL中获得每种类型的最早信息

大家都在问