牛客网SQL在线编程之较难级别

SQL74 考试分数(三)

代码

SELECT
  t1.id,
  t1.name,
  t1.score
FROM (SELECT grade.id,
             grade.language_id,
             language.name,
             grade.score,
             DENSE_RANK() over (PARTITION BY grade.language_id ORDER BY grade.score desc) as rank
     FROM grade
     JOIN language on language.id = grade.language_id) t1
where t1.rank in (1,2)
ORDER BY t1.NAME,t1.SCORE DESC,t1.ID;

知识点

• ROW_NUMBER()：ROW_NUMBER()函数作用就是将select查询到的数据进行排序，每一条数据加一个序号，他不能用做于学生成绩的排名，一般多用于分页查询

• RANK()函数：顾名思义排名函数，可以对某一个字段进行排名。row_number()并不会进行并列排序，而rank()函数会；

• DENSE_RANK()函数：是排名函数，和RANK()功能相似，也是对字段进行排名；但是二者的区别如下图：

• NTILE()函数：将有序分区中的行分发到指定数目的组中，各个组有编号，编号从1开始，就像我们说的’分区’一样 ，分为几个区，一个区会有多少个

SQL69 牛客每个人最近的登录日期(四)

代码

SELECT
  t1.date,
  sum(case when t1.rank = 1 then 1 else 0 end)
FROM (SELECT id,
             user_id,
             client_id,
             date,
             row_number() over (PARTITION BY user_id order by date) as rank
     from login) t1
 group by date;

知识点

• CASE WHEN条件表达式函数：类似JAVA中的IF ELSE语句。
  格式：
• CASE WHEN condition THEN result ELSE result END
  condition是一个返回布尔类型的表达式，如果表达式返回true，则整个函数返回相应result的值，如果表达式皆为false，则返回ElSE后result的值，如果省略了ELSE子句，则返回NULL。

SQL80 牛客的课程订单分析(四)

代码

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

知识点

group by可以对应两个聚合函数，后面跟随having来代替where

SQL60 统计salary的累计和running_total

代码

select 
  s1.emp_no,
  s1.salary,
  sum(s2.salary)
from salaries s1, salaries s2
where s1.to_date='9999-01-01' and s2.to_date='9999-01-01' and s1.emp_no >= s2.emp_no
group by s1.emp_no
order by s1.emp_no;

知识点

使用两个相同的表格对同一个字段进行对比来反映该字段值的位置

SQL68 牛客每个人最近的登录日期(三)⭐

代码

select round(count(distinct l1.user_id)*1.0/(select count(distinct user_id) from login),3)
from login l1, login l2
where l1.user_id = l2.user_id and l2.date = date(l1.date,'+1 day')

知识点

哎 很难理解呀 还在理解🧐

SQL71 牛客每个人最近的登录日期(六)

代码

select 
  t2.name as u_n,
  t1.date as date,
  sum(t1.number) over (partition by user_id order by date) as ps_num
from passing_number t1
left join user t2 on t1.user_id = t2.id
ORDER by t1.date, t2.name;

知识点

sum()函数可以使用partition by和order by来配合使用

SQL65 异常的邮件概率

代码

select
  date,
  round(sum(case type when 'no_completed' then 1 else 0 end)*1.0/count(type),3)
from email
where send_id not in (select id from user where is_blacklist = 1)
and receive_id not in (select id from user where is_blacklist = 1)
group by date;

知识点

用/来做除法的时候，要在分母处*1.0来避免整除