企业官网建设流程全解析

本文解析了SQL280题目"牛客的课程订单分析(五)"的解题思路。题目要求查询2025-10-15后购买2次及以上指定课程的用户信息，包括首次和第二次购买日期。

---

通过WITH子句创建临时表t1，使用窗口函数COUNT和ROW_NUMBER统计购买次数和序号。

---

核心技巧是使用MAX(CASE WHEN rn=1 THEN date END)获取首次购买日期：

1) CASE WHEN筛选符合条件的日期并置其他为NULL；

2) MAX聚合函数提取非NULL值；

3) GROUP BY分组确保SQL语法正确。

---

这种组合实现了行转列效果，避免了复杂连接操作。最终结果按user_id排序输出用户ID、首次/二次购买日期及总购买次数。

SQL示例

题目

SQL280 牛客的课程订单分析(五)

---

描述

有很多同学在牛客购买课程来学习，购买会产生订单存到数据库里。

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

id	user_id	product_name	status	client_id	date
1	557336	C++	no_completed	1	2025-10-10
2	230173543	Python	completed	2	2025-10-12
3	57	JS	completed	3	2025-10-23
4	57	C++	completed	3	2025-10-23
5	557336	Java	completed	1	2025-10-23
6	57	Java	completed	1	2025-10-24
7	557336	C++	completed	1	2025-10-25
8	557336	Python	completed	1	2025-10-26

第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升序排序，以上例子查询结果如下:

user_id	first_buy_date	second_buy_date	cnt
57	2025-10-23	2025-10-24	2
557336	2025-10-23	2025-10-25	3

解析:

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 t1 as ( # select # user_id, # date, # count(product_name) over(partition by user_id) as cnt, # row_number() over(partition by user_id order by date) as rn # from # order_info # where date>DATE '2025-10-15' # and # product_name in ('C++','Java','Python') # and # status='completed' # ), # t2 as( # select user_id, date as first_buy_date, cnt from t1 where rn=1 # ), # t3 as( # select user_id,date as second_buy_date from t1 where rn=2 # ) # select # t2.user_id, # t2.first_buy_date, # t3.second_buy_date, # t2.cnt # from # t2 # join t3 on t2.user_id=t3.user_id # where t2.cnt >= 2 # order by t2.user_id; # CTE 作用域的正确规则 # 在同一个 WITH 子句中： # 后面的 CTE 可以引用前面的 CTE # 最外层主查询可以引用所有 CTE #更简洁的写法（推荐） with t1 as ( select user_id, date, row_number() over(partition by user_id order by date) as rn, count(*) over(partition by user_id) as cnt from order_info where date > date '2025-10-15' and product_name in ('C++', 'Java', 'Python') and status = 'completed' ) select user_id, max(case when rn = 1 then date end) as first_buy_date, max(case when rn = 2 then date end) as second_buy_date, cnt from t1 where cnt >= 2 group by user_id, cnt order by user_id;

max(case when rn = 1 then date end) as first_buy_date 这里看不懂，为什么可以拿到数据

这涉及到 SQL 中CASE WHEN和MAX函数的组合使用技巧。让我详细解释：

核心原理：CASE WHEN 返回什么？

首先看CASE WHEN的执行逻辑：

sql

CASE WHEN rn = 1 THEN date ELSE NULL END

对于每一行数据，这个表达式会返回：

• 如果rn = 1，返回该行的date值

• 如果rn ≠ 1，返回NULL

具体数据示例

以用户 557336 为例，他的数据是：

执行CASE WHEN rn = 1 THEN date END

然后应用MAX()函数

sql

MAX(CASE WHEN rn = 1 THEN date END)

MAX()会在这 3 行数据中求最大值：

• 值列表：['2025-10-23', NULL, NULL]

• MAX()会忽略 NULL 值

• 最大值就是2025-10-23

结果：2025-10-23

同样道理获取第二次购买日期

sql

MAX(CASE WHEN rn = 2 THEN date END)

MAX()对[NULL, '2025-10-25', NULL]求最大值 =2025-10-25

为什么需要用 MAX？

有同学会问：为什么不直接写date而要用MAX？

因为GROUP BY的要求！

sql

SELECT user_id, date, -- ❌ 错误：没有在 GROUP BY 中，也不是聚合函数 cnt FROM t1 GROUP BY user_id, cnt

当使用GROUP BY时，SELECT 中的字段必须是：

1. 在GROUP BY中出现的字段，或者

2. 使用聚合函数（MAX、MIN、COUNT、SUM 等）的字段

对比两种写法

错误写法：

sql

SELECT user_id, date as first_buy_date, -- ❌ date 不在 GROUP BY 中 cnt FROM t1 GROUP BY user_id, cnt -- 只按 user_id 和 cnt 分组

这会导致错误：date字段既不在 GROUP BY 中，也不是聚合函数。

正确写法：

sql

SELECT user_id, MAX(CASE WHEN rn = 1 THEN date END) as first_buy_date, -- ✓ 使用聚合函数 MAX(CASE WHEN rn = 2 THEN date END) as second_buy_date, -- ✓ 使用聚合函数 cnt FROM t1 GROUP BY user_id, cnt

执行流程图解

完整执行示例

sql

-- 原始数据（t1） user_id | date | rn | cnt --------|------------|----|----- 57 | 2025-10-23 | 1 | 2 57 | 2025-10-24 | 2 | 2 557336 | 2025-10-23 | 1 | 3 557336 | 2025-10-25 | 2 | 3 557336 | 2025-10-26 | 3 | 3 -- 执行 GROUP BY 和聚合 SELECT user_id, MAX(CASE WHEN rn=1 THEN date END) as first_buy_date, MAX(CASE WHEN rn=2 THEN date END) as second_buy_date, cnt FROM t1 GROUP BY user_id, cnt -- 结果： user_id | first_buy_date | second_buy_date | cnt --------|----------------|-----------------|----- 57 | 2025-10-23 | 2025-10-24 | 2 557336 | 2025-10-23 | 2025-10-25 | 3

为什么用 MAX 而不是 MIN？

在这个场景下，因为每组只有一行数据满足 rn=1，所以MAX和MIN结果一样。但用MAX是习惯用法，因为：

• 最大值和最小值相同（只有一个非NULL值）

• 代码更规范

• 如果数据异常出现多个 rn=1，MAX 会取最大的日期（更合理）

总结

• CASE WHEN把需要的日期"标记"出来，其他行标记为 NULL

• GROUP BY把多行合并成一行

• MAX从合并后的多行中提取出那个非 NULL 的日期值

• 这个技巧常用于行转列（把多行中的特定值转成列）

CASE WHEN rn = 1 THEN date END，为什么可以直接返回日期？

CASE WHEN 的基本语法

sql

CASE WHEN 条件 THEN 返回值 ELSE 默认值 END

• 如果条件为TRUE，返回THEN后面的值

• 如果条件为FALSE或NULL，返回ELSE后面的值

• 如果省略ELSE，默认返回NULL

具体执行过程

对于CASE WHEN rn = 1 THEN date END：

sql

-- 完整写法（省略了 ELSE） CASE WHEN rn = 1 THEN date ELSE NULL END

逐行执行示例

以用户 557336 的数据为例：

为什么能直接返回日期？

因为THEN date中的date是当前行的字段值：

• 第1行执行时，date的值是2025-10-23

• 第1行的条件为 TRUE，所以直接返回2025-10-23（这是一个日期值）

关键点：CASE WHEN返回的不是"列"，而是具体某一行中该字段的值。

对比理解

sql

-- 错误理解：以为 CASE WHEN 返回的是整列 CASE WHEN rn = 1 THEN date END -- 以为会返回 [2025-10-23, 2025-10-25, 2025-10-26] -- 正确理解：逐行判断，每行返回该行的 date 或 NULL -- 第1行：2025-10-23 -- 第2行：NULL -- 第3行：NULL

可视化执行过程

完整示例代码

你可以实际执行看看：

sql

-- 创建测试数据 WITH test AS ( SELECT 1 AS rn, '2025-10-23' AS date UNION ALL SELECT 2, '2025-10-25' UNION ALL SELECT 3, '2025-10-26' ) SELECT rn, date, CASE WHEN rn = 1 THEN date END AS case_result FROM test;

执行结果：

总结

CASE WHEN rn = 1 THEN date END能直接返回日期，是因为：

1. 逐行计算：对每一行数据独立判断

2. 条件为 TRUE 时：直接返回该行date字段的具体值

3. 条件为 FALSE 时：返回 NULL

它不是返回整个date列，而是根据条件从每一行中挑选出符合条件的那个具体的日期值。