MySQL做题实战5||牛客的课程订单分析

简介：题目来自牛客网在线编程SQL实战，文章内容主要是讲解博主自己做题时的思路、相关见解以及要点总结。总的来说，就是明bug易改，暗bug难查，路漫漫其修远兮，吾将上下而求索！

一、题目内容一（难度：简单）

有一个订单信息表(order_info)，简况如下:

第1行表示user_id为557336的用户在2025-10-10的时候使用了client_id为1的客户端下了C++课程的订单，但是状态为没有购买成功。

第2行表示user_id为230173543的用户在2025-10-12的时候使用了client_id为2的客户端下了Python课程的订单，状态为购买成功。

。。。

最后1行表示user_id为557336的用户在2025-10-24的时候使用了client_id为1的客户端下了Python课程的订单，状态为没有购买成功。

编写要求：

请写出一个sql语句查询在2025-10-15以后状态为购买成功的C++课程或者Java课程或者Python的订单，并且按照order_info的id升序排序，以上例子查询结果如下:

 解题思路：

 解题代码：

select *
from order_info
where date>'2025-10-15'
and status='completed'
and product_name in ('C++','Java','Python')
order by id;

比较总结：

• 此题较为简单，主要考查筛选和排序。

---

二、题目内容二（难度：中等）

有一个订单信息表(order_info)，简况如下:

第1行表示user_id为557336的用户在2025-10-10的时候使用了client_id为1的客户端下了C++课程的订单，但是状态为没有购买成功。

第2行表示user_id为230173543的用户在2025-10-12的时候使用了client_id为2的客户端下了Python课程的订单，状态为购买成功。

。。。

最后1行表示user_id为557336的用户在2025-10-25的时候使用了client_id为1的客户端下了C++课程的订单，状态为购买成功。

编写要求：

请写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的user_id，并且按照user_id升序排序，以上例子查询结果如下（说明:id为4，6的订单满足以上条件，输出对应的user_id为57;id为5，7的订单满足以上条件，输出对应的user_id为557336;按照user_id升序排序。）:

解题思路：

  解题代码：

select user_id
from order_info
where date>'2025-10-15'
and status='completed'
and product_name in ('C++','Python','Java')
group by user_id
having count(user_id)>=2
order by user_id;

比较总结：

• 相比于第一题，题目要求多了个分组筛选，重难点在于区分是分组前筛选还是分组后筛选
• 分组前筛选的关键词是where，不会搭配聚合函数（如count、min等）使用
• 分组后筛选的关键词是having，一般搭配聚合函数使用

---

三、题目内容三（难度：中等）

有一个订单信息表(order_info)，简况如下:

第1行表示user_id为557336的用户在2025-10-10的时候使用了client_id为1的客户端下了C++课程的订单，但是状态为没有购买成功。

第2行表示user_id为230173543的用户在2025-10-12的时候使用了client_id为2的客户端下了Python课程的订单，状态为购买成功。

。。。

最后1行表示user_id为557336的用户在2025-10-25的时候使用了client_id为1的客户端下了C++课程的订单，状态为购买成功。

编写要求：

请写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的订单信息，并且按照order_info的id升序排序，以上例子查询结果如下（说明:id为4，6的订单满足以上条件，输出它们的对应的信息;id为5，7的订单满足以上条件，输出它们的对应的信息;按照id升序排序。）:

解题思路：

 解题代码：

select *
from order_info
where date>'2025-10-15'
and status='completed'
and product_name in ('C++','Python','Java')
and user_id in (
    select user_id
    from order_info
    where date>'2025-10-15'
    and status='completed'
    and product_name in ('C++','Python','Java')
    group by user_id
    having count(user_id)>=2)
order by id;

比较总结：

• group by 只会返回第一行数据！！！

• 相比于第二题，题目由查询符合条件的user_id变为查询符合条件的订单信息，容易导致分组聚合函数的错误使用，错误代码如下所示：

  # 错误示例：
  select *
  from order_info
  where product_name in ('C++','Java','Python')
  and status='completed'
  and date >'2025-10-15'
  group by user_id
  having count(*)>=2
  order by id;

---

四、题目内容四（难度：较难）

有一个订单信息表(order_info)，简况如下:

第1行表示user_id为557336的用户在2025-10-10的时候使用了client_id为1的客户端下了C++课程的订单，但是状态为没有购买成功。

第2行表示user_id为230173543的用户在2025-10-12的时候使用了client_id为2的客户端下了Python课程的订单，状态为购买成功。

。。。

最后1行表示user_id为557336的用户在2025-10-25的时候使用了client_id为1的客户端下了Python课程的订单，状态为购买成功。

编写要求：

请写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id ，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date ，以及购买成功的C++课程或J ava课程或Python课程的次数cnt ，并且输出结果按照user_id升序排序，上面的例子结果如下（说明：ID为4 ，6的订单满足以上条件，输出57 ，ID为4的订单为第一次购买成功，输出first_buy_date为2025年10月23日，总共成功购买了2次;ID为5 ，7 ，8的订单满足以上条件，输出557336 ，ID为5的订单为第一次购买成功，输出first_buy_date为2025年10月23日，总共成功购买了3次。）：

解题思路：

解题代码：

select user_id,min(date) as first_buy_date,count(user_id) as cnt
from order_info
where date>'2025-10-15'
and status='completed'
and product_name in ('C++','Python','Java')
group by user_id
having count(user_id)>=2
order by user_id;

比较总结：

• select可以搭配多个聚合函数一起使用，比如此题目中的count和min
• 相比于第二道题，本题的要求多了查询满足相关条件的购买日期和次数，重在考查聚合函数的使用

---

五、题目内容五（难度：困难）

有一个订单信息表(order_info)，简况如下:

第1行表示user_id为557336的用户在2025-10-10的时候使用了client_id为1的客户端下了C++课程的订单，但是状态为没有购买成功。

第2行表示user_id为230173543的用户在2025-10-12的时候使用了client_id为2的客户端下了Python课程的订单，状态为购买成功。

。。。

最后1行表示user_id为557336的用户在2025-10-26的时候使用了client_id为1的客户端下了Python课程的订单，状态为购买成功。

编写要求 ：

请写出一个sql语句查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及满足前面条件的第二次购买成功的C++课程或Java课程或Python课程的日期second_buy_date，以及购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序，以上例子查询结果如下（说明：id为4，6的订单满足以上条件，输出57，id为4的订单为第一次购买成功，输出first_buy_date为2025-10-23，id为6的订单为第二次购买，输出second_buy_date为2025-10-24，总共成功购买了2次；id为5，7，8的订单满足以上条件，输出557336，id为5的订单为第一次购买成功，输出first_buy_date为2025-10-23，id为7的订单为第二次购买，输出second_buy_date为2025-10-25，总共成功购买了3次）:

解题思路：

解题代码：

WITH base_table AS (
    SELECT *,DENSE_RANK() over (partition by user_id order by date) AS rnk
    FROM order_info
    WHERE date>'2025-10-15'
    AND status='completed'
    AND product_name IN ('C++','Java','Python')
),
table1 AS (
    SELECT user_id, date as first_buy_date
    FROM base_table
    WHERE rnk=1
),
table2 AS (
    SELECT user_id, date as second_buy_date
    FROM base_table
    WHERE rnk=2
),
table3 AS (
    SELECT user_id, COUNT(user_id) AS cnt
    FROM base_table
    GROUP BY user_id
    HAVING cnt>=2
)
SELECT DISTINCT base_table.user_id,first_buy_date,second_buy_date,cnt
FROM base_table,table1,table2,table3
WHERE base_table.user_id=table1.user_id
AND base_table.user_id=table2.user_id
AND base_table.user_id=table3.user_id
ORDER BY base_table.user_id

比较总结：

• 上面的解题思路虽然容易理解，但是写出来的代码很长，所以可以进一步优化一下代码，提高简洁性。优化思路和代码如下，在下图中，MAX(second_buy_date)也可换成MIN(second_buy_date)，本质是要对second_buy_date使用聚合函数，应对GROUP BY子句

WITH table1 AS (
    SELECT order_info.user_id, order_info.date as second_buy_date,
    DENSE_RANK() over (partition by user_id order by date) AS rnk
    FROM order_info
    WHERE date>'2025-10-15'
    AND status='completed'
    AND product_name IN ('C++','Java','Python')
)
SELECT order_info.user_id, MIN(order_info.date) AS first_buy_date,
MAX(second_buy_date), COUNT(*) AS cnt
FROM order_info INNER JOIN table1 ON order_info.user_id=table1.user_id
WHERE date>'2025-10-15'
AND status='completed'
AND product_name IN ('C++','Java','Python')
AND rnk=2
GROUP BY order_info.user_id
HAVING cnt>=2
ORDER BY order_info.user_id

• 参考了评论区其他解法，使用排序函数rank和if函数可以更加简洁高效

• 此外，还可以使用lead函数将第二次购买的日期往前挪到第一位， lead函数有三个参数，第一个参数是列名，第二个参数是偏移的offset，第三个参数是超出记录窗口时的默认值，参考评论区的解法如下：

---

六、题目内容六（难度：中等）

有一个订单信息表(order_info)，简况如下:

第1行表示user_id为557336的用户在2025-10-10的时候使用了client_id为1的客户端下了C++课程的非拼团(is_group_buy为No)订单，但是状态为没有购买成功。

第2行表示user_id为230173543的用户在2025-10-12的时候使用了client_id为2的客户端下了Python课程的非拼团(is_group_buy为No)订单，状态为购买成功。

。。。

最后1行表示user_id为557336的用户在2025-10-25的时候使用了下了C++课程的拼团(is_group_buy为Yes)订单，拼团不统计客户端，所以client_id所以为0，状态为购买成功。

有一个客户端表(client)，简况如下:

编写要求：

请写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的订单id，是否拼团以及客户端名字信息，最后一列如果是非拼团订单，则显示对应客户端名字，如果是拼团订单，则显示NULL，并且按照order_info的id升序排序，以上例子查询结果如下（说明：id为4，6的订单满足以上条件，且因为4是通过IOS下单的非拼团订单，输出对应的信息，6是通过PC下单的非拼团订单，输出对应的信息以及客户端名字;id为5，7的订单满足以上条件，且因为5与7都是拼团订单，输出对应的信息以及NULL;按照id升序排序）:

解题思路：

 解题代码：

select order_info.id,order_info.is_group_buy,if(is_group_buy='Yes',null,client.name)
from order_info
left join client
on order_info.client_id=client.id
where date>'2025-10-15'
and product_name in ('C++','Python','Java')
and status='completed'
and user_id in (
    select user_id
    from order_info
    where date>'2025-10-15'
    and product_name in ('C++','Python','Java')
    and status='completed'
    group by user_id
    having count(user_id)>=2)
order by order_info.id;

比较总结：

• 上面的解法是按题目的描述进行解题的，比较笨拙，主要用到了多表连接和子查询
• 使用窗口函数可优化代码

select table1.id, table1.is_group_buy, client.name
from (
    select *, count(*) over(partition by user_id) as cnt
    from order_info
    where date>'2025-10-15'
    and product_name in ('C++','Java','Python')
    and status='completed'
) as table1
left join client on table1.client_id=client.id
where table1.cnt>=2
order by table1.id

---

七、题目内容七（难度：较难）

有一个订单信息表(order_info)，简况如下:

第1行表示user_id为557336的用户在2025-10-10的时候使用了client_id为1的客户端下了C++课程的非拼团(is_group_buy为No)订单，但是状态为没有购买成功。

第2行表示user_id为230173543的用户在2025-10-12的时候使用了client_id为2的客户端下了Python课程的非拼团(is_group_buy为No)订单，状态为购买成功。

。。。

最后1行表示user_id为557336的用户在2025-10-25的时候使用了下了C++课程的拼团(is_group_buy为Yes)订单，拼团不统计客户端，所以client_id所以为0，状态为购买成功。

有一个客户端表(client)，简况如下:

编写要求：

请写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的来源信息，第一列是显示的是客户端名字，如果是拼团订单则显示GroupBuy，第二列显示这个客户端(或者是拼团订单)有多少订单，最后结果按照第一列(source)升序排序，以上例子查询结果如下（说明：id为4，6的订单满足以上条件，且因为4是通过IOS下单的非拼团订单，则记: IOS 1，6是通过PC下单的非拼团订单，则记: PC 1;id为5，7的订单满足以上条件，且因为5与7都是拼团订单，则记: GroupBuy 2;最后按照source升序排序。）:

解题思路：

解题代码：

select if(client.name is null,'GroupBuy',client.name) as source,count(client_id) as cnt
from order_info
left join client
on order_info.client_id=client.id
where date>'2025-10-15'
and status='completed'
and product_name in ('C++','Python','Java')
and user_id in (
    select user_id
    from order_info
    where date>'2025-10-15'
    and status='completed'
    and product_name in ('C++','Python','Java')
    group by user_id
    having count(user_id)>=2)
group by client_id
order by source

比较总结：

• 这道题在上一道题的基础上多添加了订单数量的查询，有了上一道做对的基础，这道题难度大大降低，要点在于用什么判断拼团订单
• 如果是使用窗口函数进行求解，只需在此查询基础上添加count(client_id) as cnt稍加修改即可

select if(client.name is null,'GroupBuy',client.name) as source,count(client_id) as cnt
from (
    select *, count(*) over(partition by user_id) as cnt
    from order_info
    where date>'2025-10-15'
    and product_name in ('C++','Java','Python')
    and status='completed'
) as table1
left join client on table1.client_id=client.id
where table1.cnt>=2
group by source
order by source