牛客的课程订单分析(五)题解
牛客的课程订单分析(五)
http://www.nowcoder.com/questionTerminal/348afda488554ceb922efd2f3effc427
select user_id,min(date) as first_buy_date,count(user_id) as cnt from
(select * from order_info where user_id in (select user_id from order_info where date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python')
group by user_id having count(user_id)>1) and date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python') order by id) table_a
group by user_id
然后这个五，我出题的时候没想太多，自己撸了一个比较复杂的，就是根据第一个表table_a得到的东西，查找第二个table_b(和table_a一样)表的最小时间不在table_c(和table_a一样)最小时间里面，得到的第二小的时间，然后2个表联立first_buy和second_buy，不知道刷题的各位有没有更好的解法:
select first_buy.user_id,first_buy.first_buy_date,second_buy.second_buy_date,cnt from
(select user_id,min(date) as first_buy_date,count(user_id) as cnt from
(select * from order_info where user_id in (select user_id from order_info where date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python')
group by user_id having count(user_id)>1) and date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python') order by id) table_a
group by user_id
) first_buy
join
(select user_id,min(table_b.date) as second_buy_date from
(select * from order_info where user_id in (select user_id from order_info where date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python')
group by user_id having count(user_id)>1) and date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python') order by id) table_b
where table_b.date not in
(select min(table_c.date) as c_buy_date from
(select * from order_info where user_id in (select user_id from order_info where date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python')
group by user_id having count(user_id)>1) and date>'2025-10-15' and status='completed' and product_name in('C++','Java','Python') order by id) table_c
group by user_id
)
group by table_b.user_id
)second_buy
on first_buy.user_id=second_buy.user_id
order by first_buy.user_id;
我的解法比较复杂，下面有一些比较精彩的解法:
小兔兔的:
SELECT a.user_id , a.first_buy_date , c.date , a.cnt
FROM
(SELECT user_id , MIN(date) first_buy_date , COUNT(*) cnt
FROM order_info
WHERE date > '2025-10-15'
AND status = 'completed'
AND product_name IN ('C++' , 'Java' , 'Python')
GROUP BY user_id
HAVING COUNT(*) >= 2) a
JOIN
(SELECT user_id , date
FROM
(SELECT user_id , date , RANK() OVER(PARTITION BY user_id ORDER BY date) rnk
FROM order_info
WHERE date > '2025-10-15'
AND status = 'completed'
AND product_name IN ('C++' , 'Java' , 'Python')) b
WHERE b.rnk = 2) c
ON a.user_id = c.user_id
ORDER BY a.user_id;
其中筛选的子表a是和订单(四)一样的结果，然后联立一个新的c，c里面有一个b，b和之前的(一)差不多，但是多了一个date，和rnk，来当筛选条件，b里面本身是满足订单(一)条件的，然后筛选出b的排名第二的日期，和date，联立a
那么就能得到结果了，简洁明了
ASC2050的解法:
WITH order_new AS(
SELECT *
FROM order_info o
WHERE date > '2025-10-15'
AND product_name IN ('C++', 'Java', 'Python')
AND status = 'completed'
)
SELECT
user_id,
MIN(date) AS first_buy_date,
(SELECT date
FROM order_new
WHERE user_id = o.user_id
ORDER BY date
LIMIT 1, 1) AS second_buy_date,
COUNT(id) AS cnt
FROM order_new o
GROUP BY user_id
HAVING COUNT(id) >= 2
ORDER BY user_id;
直接设定order_new为筛选完订单的新表，所有的结果也肯定是从这个新表获取的，相当聪明，省了很多代码。
从这个新表里面group by 和having获得了最小日期，然后第二小日期，自连接新表order_new，当user_id相等时，肯定都是自己的日期，比如57，购买的2个日期，57=57，日期肯定都是57的2个日期，再取第2个日期，相等聪明，很精彩的解法。
刷题的大家如果有更好的解法，可以在评论留言~
查看19道真题和解析