现有订单操作记录表 t_order_log，包含用户 ID，订单 ID，操作状态，操作时间。该表中操作状态包含下单、取消、改单，三种状态，用户取消订单后，则该订单不能再做修改。

有效订单：指未取消/改单的订单。

查询出用户最新的有效订单记录。

样例数据

+------------+------------+--------+---------------------+
| user_id    | order_id   | status | create_time         |
+------------+------------+--------+---------------------+
| U001       | ord001     | 1      | 2025-06-01 12:01:00 |
| U001       | ord001     | 3      | 2025-06-01 12:02:00 |
| U001       | ord001     | 2      | 2025-06-01 12:03:00 |
| U001       | ord002     | 1      | 2025-06-01 12:05:00 |
| U001       | ord003     | 1      | 2025-06-01 12:15:00 |
| U001       | ord003     | 2      | 2025-06-01 12:20:00 |
| U002       | ord004     | 1      | 2025-06-01 15:01:00 |
| U002       | ord004     | 2      | 2025-06-01 15:02:00 |
| U003       | ord005     | 1      | 2025-06-01 20:01:00 |
| U003       | ord006     | 1      | 2025-06-01 20:02:00 |
+------------+------------+--------+---------------------+

数据说明

  • 用户 U001 的订单 ord001，ord003 最新状态均为取消单，有效单为 ord002
  • 用户 U002 仅有一个订单 ord004，且最新状态为取消
  • 用户 U003 有两个订单 ord005，ord006 且两单均有效，应取最新单 ord006

期望结果

+------------+------------+--------+---------------------+
| user_id    | order_id   | status | create_time         |
+------------+------------+--------+---------------------+
| U001       | ord002     | 1      | 2025-06-01 12:05:00 |
| U003       | ord006     | 1      | 2025-06-01 20:02:00 |
+------------+------------+--------+---------------------+

分析

根据订单的状态及操作时间来判断是否为最新的有效订单。

SQL

获取状态为 "下单" 的记录

获取每个用户、每笔订单，状态为 "下单" 的记录。

  • 执行 SQL
select * from t_order_log where status = '1';

获取订单最后 "操作时间"

根据用户ID、订单ID分组后，获取订单最后 "操作时间"。

  • 执行 SQL
select 
    user_id, order_id, max(create_time) as max_create_time
from t_order_log 
group by user_id, order_id;

判断有效订单的 "操作时间"

若有效订单的 "操作时间" 与订单最后 "操作时间" 相等，则表示该订单为 "最新有效订单"。

with 
a as (
    select * from t_order_log where status = '1'
)
,b as (
    select 
        user_id, order_id, max(create_time) as max_create_time
    from t_order_log 
    group by user_id, order_id
)
select 
    a.*
from a inner join b 
    on  a.user_id = b.user_id
    and a.order_id = b.order_id
    and a.create_time = b.max_create_time;

+------------+------------+--------+---------------------+
| user_id    | order_id   | status | create_time         |
+------------+------------+--------+---------------------+
| U001       | ord002     | 1      | 2025-06-01 12:05:00 |
| U003       | ord005     | 1      | 2025-06-01 20:01:00 |
| U003       | ord006     | 1      | 2025-06-01 20:02:00 |
+------------+------------+--------+---------------------+

从上述结果可知，用户 U003 存在两笔订单，不符合题目要求，还需要进一步处理。

完整脚本

with 
a as (
    select * from t_order_log where status = '1'
)
,b as (
    select 
        user_id, order_id, max(create_time) as max_create_time
    from t_order_log 
    group by user_id, order_id
)
,c as (
    select 
        a.*
        ,row_number() over(partition by a.user_id order by a.create_time desc) rn
    from a inner join b 
        on  a.user_id = b.user_id
        and a.order_id = b.order_id
        and a.create_time = b.max_create_time
)
select * from c where rn = 1;

+------------+------------+--------+---------------------+
| user_id    | order_id   | status | create_time         |
+------------+------------+--------+---------------------+
| U001       | ord002     | 1      | 2025-06-01 12:05:00 |
| U003       | ord006     | 1      | 2025-06-01 20:02:00 |
+------------+------------+--------+---------------------+

建表语句

-- drop table student_score;
create table if not exists student_score (
    student string,
    subject string,
    score   bigint
)
comment '学生成绩表';

示例数据

-- drop table t_order_log;
create table if not exists t_order_log (
    user_id     string  comment '用户ID',
    order_id    string  comment '订单ID',
    status      bigint  comment '操作类型 1：下单；2：取消；3：改单',
    create_time string  comment '操作时间'
);


-- 样例数据
insert into t_order_log (user_id, order_id, status, create_time) values
    ('U001', 'ord001', 1, '2025-06-01 12:01:00'),
    ('U001', 'ord001', 3, '2025-06-01 12:02:00'),
    ('U001', 'ord001', 2, '2025-06-01 12:03:00'),
    ('U001', 'ord002', 1, '2025-06-01 12:05:00'),
    ('U001', 'ord003', 1, '2025-06-01 12:15:00'),
    ('U001', 'ord003', 2, '2025-06-01 12:20:00'),
    ('U002', 'ord004', 1, '2025-06-01 15:01:00'),
    ('U002', 'ord004', 2, '2025-06-01 15:02:00'),
    ('U003', 'ord005', 1, '2025-06-01 20:01:00'),
    ('U003', 'ord006', 1, '2025-06-01 20:02:00');

参考资料

大数据SQL-查询最新有效订单记录

原创文章，转载请注明出处：http://www.opcoder.cn/article/99/