工会网站平台建设,黄骅港金沙滩景区,如何选择网站制作公司,新媒体运营师本文基于前段时间学习总结的 MySQL 相关的查询语法#xff0c;在牛客网找了相应的 MySQL 题目进行练习#xff0c;以便加强对于 MySQL 查询语法的理解和应用。
由于涉及到的数据库表较多#xff0c;因此本文不再展示#xff0c;只提供 MySQL 代码与示例输出。
以下内容是…本文基于前段时间学习总结的 MySQL 相关的查询语法在牛客网找了相应的 MySQL 题目进行练习以便加强对于 MySQL 查询语法的理解和应用。
由于涉及到的数据库表较多因此本文不再展示只提供 MySQL 代码与示例输出。
以下内容是牛客题霸-SQL 篇的第 263 - 275 道题目的 MySQL 代码答案。 SQL 263查询每个日期登录新用户个数并按照日期升序排列熟练掌握方法一的通用方法
# 方法一
with a as(select user_id,min(date) as login_datefrom logingroup by user_id
), -- 每个用户的注册日期
log_date as(select datefrom logingroup by date
) -- 所有用户登陆的日期select ld.date as date,
ifnull(count(distinct a.user_id), 0) as new
from log_date ld
left join a
on ld.date a.login_date
group by 1# 方法二了解
select a.date as date,
sum(case when rn 1 then 1 else 0 end) as new
from(select user_id, date,row_number() over(partition by user_id order by date) rnfrom login
) a
group by 1
order by 1SQL 264查询每个日期新用户的次日留存率结果保留小数点后面3位数并按照日期升序排列难点需要掌握次日留存率计算方法
with reg as
(select user_id, min(date) as reg_datefrom logingroup by 1
), -- 每个用户的注册日期
login_dis as(selectuser_id, date as login_datefrom logingroup by 1, 2
), -- 每个用户的登陆日期
login_date as(select datefrom logingroup by 1
)# 查询所有信息
select *
# 查询要求信息
select a.date as date,
round(ifnull(count(distinct c.user_id) / count(distinct b.user_id), 0), 3) as pfrom login_date a
left join reg b
on a.date b.reg_date
left join login_dis c
on b.user_id c.user_id
and b.reg_date date_sub(c.login_date, interval 1 day)
group by 1
order by 1查询所有信息中的第一个 left join 结果
查询所有信息中的第二个 left join 结果
查询要求信息的最终结果
SQL 265查询查询刷题信息包括: 用户的名字截止到某天累计总共通过了多少题并先按照日期升序排列再按照姓名升序排列有登录却没有刷题的那一天的数据不需要输出
select name as u_n, pn.date,
sum(number) over(partition by u.id order by pn.date) as pn_num
from passing_number pn
join user u
on pn.user_id u.id
order by 2, 1SQL 266查询各个岗位平均分数并按照平均分数降序排列结果保留小数点后 3 位
select job, round(avg(score), 3) as avg
from grade
group by job
order by avg descSQL 267查询用户分数大于其所在工作平均分数的 idjobscore并按照 id 的升序排列
with a as(select *,avg(score) over(partition by job) as avg_scorefrom gradegroup by 1, 2
)select id, job, score
from a
where score avg_score
order by idSQL 268查询每个岗位分数排名前 2 名的用户并先按照 language 的 name 升序排列再按照积分降序排列最后按照 grade 的 id 升序排列
with a as(select g.id as id, language_id, score, name,dense_rank() over(partition by name order by score desc) as rnfrom grade gjoin language lon g.language_id l.id
)select a.id, name, score
from a
where a.rn 2
order by name, score desc, a.idSQL 269查询每个岗位分数升序排列之后的中位数位置的范围并按照 job 升序排列
select job,
round((case when count(*) % 2 0 then count(*) / 2 else (count(*) 1) / 2 end),0) as start,
round((case when count(*) % 2 0 then count(*) / 2 1 else (count(*) 1) / 2 end),0) as end
from grade
group by job
order by jobSQL 270查询每个岗位分数的中位数位置上的所有信息并按照 id 升序排列
with a as(select *,dense_rank() over(partition by job order by score desc) as rn,count(*) over(partition by job) as cntfrom grade
)select id, job, score, rn from a
# 中位数公式
where rn (cnt - 1) / 2 and rn (cnt 3) / 2
order by idSQL 271查询在 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 idSQL 272查询在 2025-10-15 以后同一个用户下单 2 个及 2 个以上状态为购买成功的 C 课程或 Java 课程或 Python 课程的 user_id 并按照 user_id 升序排列
select user_id
from order_info
where date 2025-10-15
and status completed
and product_name in(C, Java, Python)
group by 1
having count(*) 2
order by 1SQL 273查询在 2025-10-15 以后同一个用户下单 2 个及 2 个以上状态为购买成功的 C 课程或 Java 课程或 Python 课程的订单信息 并按照 id 升序排列
with a as(select *, count(*) over(partition by user_id) cntfrom order_infowhere date 2025-10-15and status completedand product_name in (C, Java, Python)
)select a.id, a.user_id, a.product_name, a.status, a.client_id, a.date
from a
where a.cnt 2
order by 1SQL 274查询在 2025-10-15 以后如果有一个用户下单 2 个及 2 个以上状态为购买成功的 C 课程或 Java 课程或 Python 课程那么输出这个用户的user_id以及满足前面条件的第一次购买成功的 C 课程或 Java 课程或 Python 课程的日期 first_buy_date以及所有日期里购买成功的 C 课程或 Java 课程或 Python 课程的次数 cnt并且输出结果按照 user_id 升序排序难点需要熟练掌握方法二和方法三的通用方法
# 方法一
select user_id,
min(date) as first_buy_date,
count(*) as cnt
from order_info
where date 2025-10-15
and status completed
and product_name in(C, Java, Python)
group by 1
having cnt 2
order by 1# 方法二
with a as(select user_id,date,row_number() over(partition by user_id order by date) as rn,count(*) over(partition by user_id) as cntfrom order_infowhere date 2025-10-15and status completedand product_name in(C, Java, Python)
)select user_id, date as first_buy_date, cnt
from a
where rn 1 and cnt 2
order by 1# 方法三
with a as(select user_id,date,first_value(date) over(partition by user_id order by date) as first_buy_date,count(*) over(partition by user_id) as cntfrom order_infowhere date 2025-10-15and status completedand product_name in(C, Java, Python)
)select user_id, first_buy_date, cnt
from a
group by 1, 2
having cnt 2
order by 1SQL 275查询在 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 升序排序
with a as(select user_id, first_value(date) over(partition by user_id order by date) as first_buy_date,lead(date, 1) over(partition by user_id order by date) as second_buy_date,row_number() over(partition by user_id order by date) as rn,count(*) over(partition by user_id) as cntfrom order_infowhere date 2025-10-15and status completedand product_name in(C, Java, Python)
)select user_id, first_buy_date,
second_buy_date, cnt
from a
where rn 1 and cnt 2
order by 1
