SQL-牛客/Leetcode

MySql和Sqlite的对比
牛客

1.查找最晚入职员工的所有信息

SELECT * FROM employees A
where hire_date=(select max(hire_date) from employees );

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

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

SQL LIMIT子句
3.查找各个部门当前领导当前薪水详情以及其对应部门编号dept_no

select s.* ,d.dept_no
from salaries as s 
join dept_manager as d 
on s.emp_no=d.emp_no
WHERE s.to_date='9999-01-01'
AND d.to_date='9999-01-01'

4.查找所有已经分配部门的员工的last_name和first_name

select e.last_name,e.first_name,d.dept_no
from dept_emp as d left join employees as e
on e.emp_no=d.emp_no;

5.查找所有员工的last_name和first_name以及对应部门编号dept_no

select last_name,first_name,dept_no
from employees left join dept_emp
on employees.emp_no =dept_emp.emp_no

7.查找薪水涨幅超过15次的员工号emp_no以及其对应的涨幅次数t

select emp_no,count(emp_no) as aa from salaries
GROUP BY emp_no HAVING aa > 15

8.找出所有员工当前具体的薪水salary情况

select distinct salary from salaries
order by salary desc

10.获取所有非manager的员工emp_no

select emp_no from employees
where emp_no not in (select emp_no from dept_manager)

11.获取所有员工当前的manager

select d.emp_no,m.emp_no from dept_emp d join dept_manager m
on d.dept_no = m.dept_no 
where d.emp_no <> m.emp_no

12.获取所有部门中当前员工薪水最高的相关信息

select dept_no,emp_no,max(sly) over(partition by dept_no) maxSalary
from (select d.emp_no,d.dept_no,s.salary sly 
      from dept_emp  d
      join salaries  s
      on d.emp_no=s.emp_no
      where d.to_date='9999-01-01' and s.to_date='9999-01-01'
     ) l
order by dept_no

木有通过，但是没有找出来bug在哪里
15查找employees表所有emp_no为奇数

select * 
from employees
where emp_no%2=1 and last_name!='Mary'
order by hire_date desc

64.找到每个人的任务

select person.id,name,content
from   person left join task 
              on person.id=task.person_id

65.异常的邮件概率

select date,round(1.0*sum(case when type='no_completed' then 1 else 0 end)/
                      count(type),3) p
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
order by date

66.牛客每个人最近的登录日期(一)

select user_id ,max(date) d from login
group by user_id
order by user_id

67.牛客每个人最近的登录日期(二)

select user.name u_n,client.name c_n,max(date)
from login right join user on user.id=login.user_id
           join client on client.id=login.client_id
group by user.name
order by user.name

木有通过，还在找bug。。。
68.牛客每个人最近的登录日期(三)

elect round(1.0*sum(case when l2.user_id=l1.user_id and l1.date=date_add(l2.d,interval 1 day) then 1 else 0 end)/
            count(distinct l1.user_id),3) p
from login l1,(select user_id,min(date) d from login group by user_id) l2

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

select date,sum(case when t_rank=1 then 1 else 0 end) new
from (select user_id,date,row_number() over(partition by user_id order by date) t_rank from login) l
group by date
order by date

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

SELECT a.date, ROUND(COUNT(b.user_id) * 1.0/COUNT(a.user_id), 3) AS p
FROM (
    SELECT user_id, MIN(date) AS date
    FROM login
    GROUP BY user_id) a
LEFT JOIN login b
ON a.user_id = b.user_id
AND b.date = date_add(a.date, interval 1 day)
GROUP BY a.date
UNION
SELECT date, 0.000 AS p
FROM login
WHERE date NOT IN (
    SELECT MIN(date)
    FROM login
    GROUP BY user_id)
ORDER BY date;

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

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

72.考试分数(一)

select job,round(avg(score),3) avg
from grade
group by job
order by avg desc

73.考试分数(二)

select id,job,score
from grade g1
where score > (select avg(score)
               from grade g2
               where g2.job=g1.job
               group by job)
order by id

74.考试分数(三)

select g.id id,name,score 
from   (select *,dense_rank() over(partition by language_id order by score desc) ranking
        from grade) g join language on g.language_id=language.id
where ranking=1 or ranking=2
order by name asc,score desc

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

with info as 
( 
    select *,count(id) over(partition by user_id ) cnt,
           row_number() over(partition by user_id order by date) ranking
      from order_info
     where status like 'c%' 
       and  product_name in ('C++','Java','Python') 
       and  date >'2025-10-15'
) 
select user_id,
       -- 不明白为什么要加min，row_number()本身不就生成递增的序列吗？
       min(case when ranking=1 then date end) as first_buy_date,
       min(case when ranking=2 then date end) as second_buy_date,
       cnt
from info
where cnt>1
group by user_id

87.最差是第几名(一)

select c1.grade,sum(case when c1.grade>= c2.grade then c2.number else 0  end) t_rank
from class_grade c1,class_grade c2
group by c1.grade
order by c1.grade

88.最差是第几名(二)

select grade from (select grade, 
                   (select sum(number) from class_grade) as t,
                          sum(number) over(order by grade) as a,
                          sum(number) over(order by grade desc) as b
                  from class_grade) l
where t * 1.0/2 <= a and t * 1.0/2 <= b
order by grade

89.获得积分最多的人(一)

select name,max(gm)
from user join (select user_id,sum(grade_num) gm,type from grade_info
                where type='add' group by user_id ) g
           on user.id=g.user_id

90.获得积分最多的人(二)

select l.user_id id,name,l.grade_sum
from (select user_id,sum(grade_num) grade_sum, 
            dense_rank() over(order by sum(grade_num) desc) ranking
      from grade_info group by user_id) l 
      join user on l.user_id=user.id
where l.ranking=1
order by l.user_id

91.获得积分最多的人(三)

select gi1.user_id id,name,gi1.grade_sum
from 
(select user_id,sum(if(type='add',grade_num,-grade_num)) as grade_sum,
        dense_rank() over(order by sum(if(type='add',grade_num,-grade_num)) desc) d_rank
 from grade_info group by user_id) as gi1
join user
on gi1.user_id=user.id
where gi1.d_rank=1      
order by gi1.user_id