牛客sql刷题所得

SQL253 获取有奖金的员工相关信息。

我的代码

SELECT eb.emp_no
      ,first_name
      ,last_name
      ,btype
      ,salary
      ,(case when btype = 1 then  salary*0.1
             when btype = 2 then  salary*0.2
             else salary*0.3
       end) bonus
FROM emp_bonus eb
left join  salaries s
on eb.emp_no = s.emp_no
left join employees es
on s.emp_no = es.emp_no
where to_date = '9999-01-01'

反思1：其中where其实可以用and代替

反思2：因为题目中奖金类型是由1、2、3数字表示的，奖金所得正好是1为10%，2为20%，3为30%，所以奖金列可以用 salary * btype*0.1表示，比case when执行快

代码如下

select e.emp_no
        ,e.first_name
        ,e.last_name
        ,b.btype
        ,s.salary
        ,(s.salary*b.btype*0.1) as bonus
from employees e
join salaries s on e.emp_no=s.emp_no
join emp_bonus b on e.emp_no=b.emp_no
where s.to_date='9999-01-01';

 SQL196 查找入职员工时间排名倒数第三的员工所有信息

我的错误代码

select *
from employees
where emp_no =
(select emp_no
from employees
order by hire_date desc
limit 2,1)

错误点在于，时间排名倒数第三可能不止一个员工，子查询的结果如果为时间，那么就能找出当天所有的学生编号

正确代码

select *
from employees
where hire_date =
(select hire_date
from employees
group by hire_date
order by hire_date desc
limit 2,1)

SQL255 给出employees表中排名为奇数行的first_name

给姓名行排序：复制原表进行笛卡尔积，利用first_name字段首字母的ascii编码进行大小比对得出大小

select e1.first_name
      ,ascii(e1.first_name)
      ,(select count (*) from employees e2 where e1.first_name>=e2.first_name) rownum
from employees e1

得到结果

rownum列放在where子查询里，并将rownum列取余后，为1的即是奇数 

这样就不会打乱原表的排序

代码如下

select e1.first_name
from employees as e1
where (select count(*) from employees as e2 where e1.first_name >= e2.first_name)%2=1

SQL257 刷题通过的题目排名

解法1：窗口函数

select id
       ,number
       ,dense_rank()over(order by number desc)t_rank
from passing_number
order by t_rank

解法二：子查询

select p1.id
      ,p1.number
      ,(select count(distinct number) 
      from passing_number p2
      where p1.number <= p2.number)t_rank
from passing_number p1
order by number desc,id

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

新用户的次日留存率

法一：

select round(
    (select count(l.user_id)
    from login l
    join 
      (select user_id
             ,date_add(min(date),interval 1 day) 次日登录时间
       from login
       group by user_id
      )t1
    on l.user_id = t1.user_id
    and l.date = t1.次日登录时间
     )
/count(distinct user_id),3)  as p
from login

法二：

select
round(count(user_id)/(select count(distinct user_id) from login) ,3)
from login
where (user_id,date)
in (select user_id,date_add(min(date),interval 1 day) from login group by user_id);

所有用户的次日留存率

select round(count(distinct l1.user_id)/(select count(distinct user_id)  from login),3)
from login l1, login l2
where l1.user_id = l2.user_id and l2.date = date_add(l1.date,interval 1 day)

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

法一： 表连接

select l1.date
      ,count(l2.user_id)new
from (select distinct date from login)l1
left join (select user_id,min(date) date from login group by user_id) l2
on l1.date = l2.date
group by l1.date

法二：select 子查询

select date
       ,count(case when (user_id,date) in
       (select user_id,min(date)from login group by user_id)
       then user_id else null end) new
from login
group by date
order by date

SQL264 牛客每个人最近的登录日期(五) 

法一： select 子查询

select date
      ,round(ifnull(sum(a)/sum(b),0),3)p
from
(select date
       ,case when (user_id,lead(date)over(partition by user_id order by date)) in 
       (select user_id,date_add(min(date),interval 1 day) from login group by user_id)
       then 1 else 0 end as a
       ,case when (user_id,date) in
       (select user_id,min(date) from login group by user_id)
       then 1 else 0 end as b
from login
)t1
group by date

法二：表连接

select t0.date,
ifnull(round(count(distinct t2.user_id)/(count(t1.user_id)),3),0)
from
(
    select date
    from login
    group by date
) t0
left join
(
    select user_id,min(date) as date
    from login
    group by user_id
)t1
on t0.date=t1.date
left join login as t2
on t1.user_id=t2.user_id and datediff(t2.date,t1.date)=1
group by t0.date

SQL269 考试分数(四)

法一：group by 后 用case when + count（）区分分区总数奇偶情况

select job
      ,case when count(id)%2 = 1 then round((count(id)+1)/2,0) 
      else round(count(id)/2,0) end start
      ,case when count(id)%2 = 1 then round((count(id)+1)/2,0) 
      else round(count(id)/2+1,0) end end
from grade
group by job
order by job

法二：用floor()向下取整和ceil()向上取整区分奇偶中位数

select job
      ,floor((count(*)+1)/2) 'start'
      ,ceil((count(*)+1)/2) 'end'
from grade
group by job
order by job

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

难点：第二次购买的日期：group by后用max函数和case when

select user_id
      ,max(case when oi1.rank = 1 then oi1.date else 0 end) 'first_buy_date'
      ,max(case when oi1.rank = 2 then oi1.date else 0 end)'second_buy_date'
      ,count(1)'cnt'
from
(select user_id
      ,date
      ,row_number()over(partition by user_id order by date)'rank'
from order_info
where date >= '2025-10-15'
and product_name in ('C++','Java','Python')
and status = 'completed'
)oi1
group by user_id
having count(1)>=2
order  by user_id