首页 > 试题广场 >

牛客的课程订单分析(六)

[编程题]牛客的课程订单分析(六)

热度指数：81509 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

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

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

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

第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)，简况如下:

id	name
1	PC
2	Android
3	IOS
4	H5

请你写出一个sql语句查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的订单id，是否拼团以及客户端名字信息，最后一列如果是非拼团订单，则显示对应客户端名字，如果是拼团订单，则显示NULL，并且按照order_info的id升序排序，以上例子查询结果如下:

id	is_group_buy	client_name
4	No	IOS
5	Yes	NULL
6	No	PC
7	Yes	NULL

解析:

id为4，6的订单满足以上条件，且因为4是通过IOS下单的非拼团订单，输出对应的信息，6是通过PC下单的非拼团订单，输出对应的信息以及客户端名字;

id为5，7的订单满足以上条件，且因为5与7都是拼团订单，输出对应的信息以及NULL;

按照id升序排序

示例1

输入

drop table if exists order_info;
drop table if exists client;
CREATE TABLE order_info (
id int(4) NOT NULL,
user_id int(11) NOT NULL,
product_name varchar(256) NOT NULL,
status varchar(32) NOT NULL,
client_id int(4) NOT NULL,
date date NOT NULL,
is_group_buy varchar(32) NOT NULL,
PRIMARY KEY (id));

CREATE TABLE client(
id int(4) NOT NULL,
name varchar(32) NOT NULL,
PRIMARY KEY (id)
);

INSERT INTO order_info VALUES
(1,557336,'C++','no_completed',1,'2025-10-10','No'),
(2,230173543,'Python','completed',2,'2025-10-12','No'),
(3,57,'JS','completed',0,'2025-10-23','Yes'),
(4,57,'C++','completed',3,'2025-10-23','No'),
(5,557336,'Java','completed',0,'2025-10-23','Yes'),
(6,57,'Java','completed',1,'2025-10-24','No'),
(7,557336,'C++','completed',0,'2025-10-25','Yes');

INSERT INTO client VALUES
(1,'PC'),
(2,'Android'),
(3,'IOS'),
(4,'H5');

输出

4|No|IOS
5|Yes|None
6|No|PC
7|Yes|None

算法知识视频讲解

Mysql

牛客541781620号

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

发表于 2022-05-19 15:23:12 回复(0)

Mysql

Hikvision_boy

with q1 as
(select *,count(status)over(partition by user_id) aa from order_info where date >'2025-10-15'

and status='completed' and product_name in ('C++','Java','Python'))//还是先求q1，任何join q2，然后加条件大于1 并且left join 左连接//

select q1.id,q1.is_group_buy, q2.name from q1
left join client q2 on q1.client_id=q2.id where q1.aa>1 order by q1.id

发表于 2022-02-22 23:17:56 回复(0)

牛客898694082号

反正拼团不统计client_id用left join

select a.id,a.is_group_buy,name from order_info a
left join client b on a.client_id=b.id
where date>'2025-10-15' and status='completed' and
product_name in('C++','java','python')
and user_id in(select user_id from
( select user_id,count(user_id) a from order_info where date>'2025-10-15' and status='completed' and
product_name in('C++','java','python')group by user_id) a where a.a>1 )

发表于 2021-08-23 09:58:10 回复(0)

牛客475728725号

select y.id,y.is_group_buy,c.name from
(select x.id,x.is_group_buy,x.client_id # 将满足条件的订单全部找出来和x.id找出来
from (select id,is_group_buy,(case when client_id='0' then null else client_id end)as client_id #用了个判断条件，后面发现不用也可以
,count(user_id)over(partition by user_id) as cnt from order_info
where product_name in('C++','Java','Python')

and status='completed' and date>'2025-10-15')as x where x.cnt>=2)as y

left join client as c on
y.client_id=c.id
order by y.id

发表于 2021-04-21 15:06:06 回复(0)

wengJJ

善于利用窗口函数和连接的特性，可以缩减很多代码量

select t.id,t.is_group_buy,c.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') t
left join client c on t.client_id=c.id
where t.cnt>=2
order by t.id

发表于 2021-04-08 14:47:02 回复(10)

BWTNjIrMFoan

http://https://blog.csdn.net/dhr223/article/details/107413344

思路：使用窗口函数来计算购买完成两次以上的，使用左连接来获取client名字

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

特此注意在窗口函数中不要加order by ，如果向下面一样，就会错误：

count(1) over (partition by user_id order by date) cnt

原因是窗口函数中 order by 不仅仅是排序的功能，实质上是累加的功能。

具体分析参考此博客：https://blog.csdn.net/dhr223/article/details/107413344

发表于 2021-04-10 11:16:05 回复(2)

Mysql

牛客184739658号

-- 方法一：初始笨拙法
select o.id
,is_group_buy
,case when is_group_buy = 'Yes' then 'None'
      when is_group_buy='No' then name end client_name 
from order_info o
left join client c -- 这题要left join
on o.client_id = c.id
where date > '2025-10-15'
and status = 'completed'
and product_name IN ('C++','Java','Python')
and 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(status) >= 2
)
order by o.id

-- 方法二：由于使用左连接不需要用case when
select o.id
,is_group_buy
,name client_name 
from order_info o
left join client c -- 这题要left join
on o.client_id = c.id
where date > '2025-10-15'
and status = 'completed'
and product_name IN ('C++','Java','Python')
and 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(status) >= 2
)
order by o.id

-- 方法三：用窗口函数进一步简化
select a.id
,is_group_buy
,name client_name 
from
(
    select *
    ,count(*)over(partition by user_id) cnt
    from order_info
    where date > '2025-10-15'
    and status = 'completed'
    and product_name IN ('C++','Java','Python')
) a
left join client c -- 这题要left join
on a.client_id = c.id
where cnt >= 2
order by a.id

发表于 2021-07-22 10:04:31 回复(1)

OFFER冲冲冲2021

SELECT o.id, o.is_group_buy, c.name AS client_name
FROM order_info o
LEFT JOIN client c
ON o.client_id = c.id
WHERE o.date > '2025-10-15'
AND product_name IN ('C++', 'Java', 'Python')
AND status = 'completed'
AND 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(product_name) >= 2
);

发表于 2021-03-02 16:32:28 回复(3)

Mysql

牛客631826116号

#观察输出发现id是4567，4和6是同一个客户，5和7是同一个客户，所以不能单纯的使用group by,必须先找到4567对应的客户
select user_id from order_info o 
where date>'2025-10-15' and status='completed' and product_name in ('C++','Java','Python')
group by user_id
having count(o.id)>1;

输出内容：
57
557336

#找到客户后，如下代码可以找出这些客户的所有订单
select o.id,is_group_buy,name client_name from order_info o
left join client c on o.client_id=c.id
where user_id in 
    (select user_id from order_info o 
    where date>'2025-10-15' and status='completed' and product_name in ('C++','Java','Python')
    group by user_id
having count(o.id)>1) 
order by o.id；

输出内容：
1|No|PC
3|Yes|None
4|No|IOS
5|Yes|None
6|No|PC
7|Yes|None

#在上边的订单中，继续筛选
select o.id,is_group_buy,name client_name from order_info o
left join client c on o.client_id=c.id
where user_id in 
    (select user_id from order_info o 
    where date>'2025-10-15' and status='completed' and product_name in ('C++','Java','Python')
    group by user_id
    having count(o.id)>1) 
and date>'2025-10-15' and status='completed' and product_name in ('C++','Java','Python')
order by o.id;

发表于 2021-07-14 09:19:26 回复(1)

Mysql

Stzolo

#思路利用子查询进行解题。

select  a.id, is_group_buy, 
# 这里直接用 b.name  也可以达到效果（因为client表没有0这个ID，故团购为Yse的id=0，则不与client匹配，结果为null)
case when is_group_buy='Yse' then '' else b.name end as client_name
from (select id, user_id, client_id, is_group_buy, count(id) over(partition by user_id order by user_id) as t_count
    from order_info a
    where date > '2025-10-15'
    and status = 'completed'
    and product_name in ('C++','Python','Java')
    order by a.id ) a
left join client b on a.client_id = b.id
where t_count>1
order by a.id

发表于 2022-04-19 17:22:41 回复(0)

Mysql

Vivimore

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

select order_info.id,is_group_buy,name client_name
from tmp join order_info using(user_id) left join client 
on order_info.client_id = client.id
where `status`='completed' and date > '2025-10-15' and product_name in ('C++','Python','Java')
order by order_info.id

发表于 2022-03-27 19:39:21 回复(0)

Mysql

牛客472346081号

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

发表于 2021-12-09 18:14:26 回复(0)

Mysql

JeFfrEylllc

SELECT t.id, is_group_buy, c.name
FROM (SELECT *, count(*) OVER (PARTITION BY user_id) AS cnt
    FROM order_info
    WHERE product_name IN ('C++','Python','Java')
    AND date >'2025-10-15'
    AND status = 'completed') t
LEFT JOIN client c ON t.client_id = c.id AND is_group_buy = 'No'
WHERE t.cnt >1
ORDER BY t.id

先做一个子表包含每个用户id符合条件的count数量

然后join时候再加上is_group_by = 'no' 这样就可以在no的时候显示出名称，yes时候是null

发表于 2021-09-02 11:58:14 回复(0)

Mysql

牛客923860633号

解法一

不使用窗口函数

select t.id, t.is_group_buy, c.name
from (
    select *
    from order_info
    where date > '2025-10-15'
    and status = 'completed'
    and product_name in ('C++', 'Java', 'Python')
    and 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(distinct product_name) >= 2
                  )
    order by id
) as t
left join client c
on t.client_id = c.id;

这道题可以理解两个表连接
1. 选择出满足下单日期，下单课程和下单数的表一
2. 在表一的情况下还要显示客户端名

表一的要求跟第三题是一样的（看我前面第三题SQL79）

select *
from order_info
where date > '2025-10-15'
and status = 'completed'
and product_name in ('C++', 'Java', 'Python')
and 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(distinct product_name) >= 2
              )
order by id;

用表一连接client表，显示客户端名
1. 注意到is_group_buy为Yes时，client_id的值为0
2. 而client表中没有id为0的
3. 通过左连接就可以实现非拼团订单，则显示对应客户端名字，如果是拼团订单，则显示NULL

解法二

使用窗口函数

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

编辑于 2021-10-02 16:30:51 回复(1)

Followyoyrheart

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

发表于 2021-04-08 14:39:06 回复(1)

鸭梨君

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

发表于 2021-03-15 20:36:50 回复(0)

simple_sunshine

# 
select o_in.id,o_in.is_group_buy,client.name
from ( # 将满足条件的订单全部找出来，该查询结果相当于题 牛客的订单分析（三）
    select id,client_id,is_group_buy
    from order_info
    where user_id in (
        select user_id
        from order_info
        where product_name in ('C++','Java','Python')
        and status = 'completed'
        and datediff(date,'2025-10-15') > 0
        group by user_id
        having count(*)>= 2
    )
    and product_name in ('C++','Java','Python')
    and status = 'completed'
    and datediff(date,'2025-10-15') > 0
) o_in left join client 
    on o_in.client_id = client.id
order by o_in.id;

编辑于 2021-03-11 09:32:45 回复(0)

牛客55779908号

select ac.id,ac.is_group_buy,ac.name from
(
select o.id,o.is_group_buy,cl.name,
count(user_id) over(partition by user_id) c from order_info o
left join client cl on o.client_id=cl.id
where timestampdiff(day,date,'2025-10-15')<0
and status='completed'
and product_name in ('C++','Java','Python')
) ac
where ac.c>=2
order by ac.id

发表于 2021-03-01 16:58:00 回复(0)

Mysql

不懂业务数据的运营不是好产品

对于如何实现client_id 到 client_name的转换，两种方法

-- 子查询传入统计范围内的订单id、is_gropu_by、client_id
SELECT
    t1.id,
    t1.is_group_buy,
    CASE t1.client_id  --通过case转换 id -name
        WHEN 1 THEN 'PC' 
        WHEN 2 THEN 'Android'
        WHEN 3 THEN 'IOS'  
        WHEN 4 THEN 'H5' 
        ELSE NULL
    END
FROM
    (select
        id,
        user_id,
        is_group_buy,
        client_id,
        count(id) over(partition by user_id)  as cnt
    from
        order_info
    where
        date > '2025-10-15'
        and status ='completed'
        and product_name in('C++','Java','Python')
    ) t1 
WHERE
    t1.cnt>=2
ORDER BY
    t1.id asc;

以及连接查询

SELECT
    t1.id,
    t1.is_group_buy,
    t2.name as client_name
FROM
    (select
        id,
        user_id,
        is_group_buy,
        client_id,
        count(id) over(partition by user_id)  as cnt
    from
        order_info
    where
        date > '2025-10-15'
        and status ='completed'
        and product_name in('C++','Java','Python')
    ) t1 
    left join  client t2 
    on t1.client_id = t2.id
WHERE
    t1.cnt>=2
ORDER BY
    t1.id asc;

发表于 2024-07-08 09:39:18 回复(0)

Mysql

War张

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

发表于 2023-11-14 11:16:40 回复(0)