1.题目

现有一张订单表 t_order 有订单ID、用户ID、商品ID、购买商品数量、购买时间，请查出订单量前3，且存在某个商品购买了2个或以上的用户。样例数据如下：

| 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.题目分析：

需要查询出在某个商品上购买了2个或两个以上的用户，用户可能是一个订单直接购买了2个或者两个以上，也可能是分多次购买；（举例：用户1在1001商品上分两次，每次购买了1个商品，共计2个，用户1符合条件；
用户3，分别在1001,1002,1003三个商品上购买了一单，所以不符合条件；）
计算每个用户的订单量；
查询出符合条件，订单量前3的用户

3.SQL

step1:查询符合购买2个或者以上商品的用户

select    user_idfrom    (        select            user_id,            product_id,            sum(quantity) as product_u_nums        from t_order        group by user_id,product_id        having sum(quantity) >=2    ) tgroup by user_id;

查询结果

step2:计算每个用户的订单量

select user_id,count(order_id) as order_numfrom t_order t1group by user_id

查询结果

step3:关联step1和step2的结果，得出最后结果

select    t1.user_idfrom(    select    user_id,    count(order_id) as order_num    from t_order t1    group by user_id) t1join(select    user_idfrom    (        select            user_id,            product_id,            sum(quantity) as product_u_nums        from t_order        group by user_id,product_id        having sum(quantity) >=2    ) tgroup by user_id) t2on t1.user_id = t2.user_idorder by t1.order_num desclimit 3

查询结果

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总结大厂真题-订单量前3，且存在某个商品购买了2个或以上的用户

最新文章