1.题目

现有一张订单表 t_order 有订单ID、用户ID、商品ID、购买商品数量、购买时间，请查询出每个用户的第一条记录和最后一条记录。样例数据如下：

| order_id | user_id | product_id | quantity | purchase_time       |
|----------|---------|------------|----------|---------------------|
| 1        | 1       | 1001       | 1        | 2023-03-13 08:30:00 |
| 2        | 1       | 1002       | 1        | 2023-03-13 10:45:00 |
| 3        | 1       | 1001       | 1        | 2023-03-13 10:45:01 |
| 4        | 2       | 1001       | 3        | 2023-03-13 14:20:00 |
| 5        | 3       | 1003       | 1        | 2023-03-13 16:15:00 |
| 6        | 3       | 1002       | 1        | 2023-03-13 12:10:00 |
| 7        | 3       | 1001       | 1        | 2023-03-13 12:10:01 |
| 8        | 4       | 1002       | 2        | 2023-03-13 09:00:00 |
| 9        | 4       | 1003       | 1        | 2023-03-13 11:30:00 |
| 10       | 4       | 1004       | 3        | 2023-03-13 13:40:00 |
| 11       | 4       | 1001       | 1        | 2023-03-13 17:25:00 |
| 12       | 4       | 1002       | 2        | 2023-03-13 15:05:00 |
| 13       | 4       | 1004       | 1        | 2023-03-13 11:55:00 |

2.分析

获取记录，一般都会用到开窗函数，本题也是如此，row_number();
本题特殊之处在于，同一个规则，两种不同的排序方式；一种解决方案是union all，另外一种方式是，直接开窗两次获得两个不同的字段，然后使用or获取最后结果。两种方案得出的结果不同，因为如果存在用户只有一条记录，则第一种解决方案会有两条相同记录（当然，如果使用union可以避免），第二种方法则直接去重了；
个人不是很喜欢这个题目，不知道具体的考点，题目还容易有歧义

3.SQL

方法一：

select    order_id,    user_id,    product_id,    quantity,    purchase_timefrom    (        select            order_id,            user_id,            product_id,            quantity,            purchase_time,            row_number()over(partition by user_id order by purchase_time asc) as asc_rn        from t_order    ) t1where t1.asc_rn =1union allselect    order_id,    user_id,    product_id,    quantity,    purchase_timefrom    (        select            order_id,            user_id,            product_id,            quantity,            purchase_time,            row_number()over(partition by user_id order by purchase_time desc) as desc_rn        from t_order    ) t2where t2.desc_rn =1

结果

使用union替代union all的结果

方法二：

select    order_id,    user_id,    product_id,    quantity,    purchase_timefrom    (        select            order_id,            user_id,            product_id,            quantity,            purchase_time,            row_number()over(partition by user_id order by purchase_time asc) as asc_rn,            row_number()over(partition by user_id order by purchase_time desc) as desc_rn        from t_order    ) t1where t1.asc_rn =1 or t1.desc_rn =1

结果：

4.数据准备

1.建表语句

CREATE TABLE t_order (    order_id INT,    user_id INT,    product_id INT,    quantity INT,    purchase_time TIMESTAMP)ROW FORMAT DELIMITED FIELDS TERMINATED BY ','STORED AS TEXTFILE;

2.数据插入语句

INSERT INTO t_order VALUES(1, 1, 1001, 1, '2023-03-13 08:30:00'),(2, 1, 1002, 1, '2023-03-13 10:45:00'),(3, 1, 1001, 1, '2023-03-13 10:45:01'),(4, 2, 1001, 3, '2023-03-13 14:20:00'),(5, 3, 1003, 1, '2023-03-13 16:15:00'),(6, 3, 1002, 1, '2023-03-13 12:10:00'),(7, 3, 1001, 1, '2023-03-13 12:10:01'),(8, 4, 1002, 2, '2023-03-13 09:00:00'),(9, 4, 1003, 1, '2023-03-13 11:30:00'),(10, 4, 1004, 3, '2023-03-13 13:40:00'),(11, 4, 1001, 1, '2023-03-13 17:25:00'),(12, 4, 1002, 2, '2023-03-13 15:05:00'),(13, 4, 1004, 1, '2023-03-13 11:55:00');

点点关注不迷路，天天学习有进步

分享↓收藏↓点赞↓再看↓

ITPub博客

SQL总结大厂真题-查询每个用户的第一条和最后一条记录

最新文章