牛客SQL-较难篇

题库

以下是牛客SQL的较难、困难所有题解

较难

获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

获取当前薪水第二多的员工的emp_no以及其对应的薪水salary牛客题霸牛客网 (nowcoder.com )

题目分析：题解 | #获取当前薪水第二多的员工#_牛客网 (nowcoder.com )

SELECT s.emp_no,s.salary,
    e.last_name,e.first_name
FROM salaries s
    JOIN employees e USING(emp_no)
WHERE s.salary = (
    SELECT max(salary)
    FROM salaries
    WHERE salary < (
        SELECT max(salary)
        FROM salaries
        WHERE to_date = '9999-01-01'
    )
    and to_date = '9999-01-01'
) and s.to_date = '9999-01-01'

对所有员工的薪水按照salary降序进行1-N排名

对所有员工的薪水按照salary降序进行1-N的排名牛客题霸牛客网 (nowcoder.com )

题解：牛客网 - 找工作神器|笔试题库|面试经验|实习招聘内推，求职就业一站解决_牛客网 (nowcoder.com )

SELECT emp_no,salary,
  dense_rank () over (ORDER BY salary DESC) AS `rank` 
FROM salaries 
WHERE to_date = '9999-01-01' ;

获取所有非manager员工当前的薪水情况

获取所有非manager员工当前的薪水情况牛客题霸牛客网 (nowcoder.com )

题解：牛客网 - 找工作神器|笔试题库|面试经验|实习招聘内推，求职就业一站解决_牛客网 (nowcoder.com )

SELECT dept_no,emp_no,salary
FROM dept_emp
    JOIN salaries USING(emp_no)
WHERE emp_no NOT IN (
    SELECT emp_no
    FROM dept_manager
)

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

获取有奖金的员工相关信息。牛客题霸牛客网 (nowcoder.com )

SELECT emp_no,first_name,last_name,btype,salary,
    ROUND(
        CASE
            WHEN btype = 1 THEN salary * 0.1
            WHEN btype = 2 THEN salary * 0.2
            ELSE salary * 0.3
        END
    ,1) bonus
FROM employees
    JOIN emp_bonus USING(emp_no)
    JOIN salaries USING(emp_no)
WHERE to_date = '9999-01-01'

统计salary的累计和running_total

统计salary的累计和running_total牛客题霸牛客网 (nowcoder.com )

题解：牛客网 - 找工作神器|笔试题库|面试经验|实习招聘内推，求职就业一站解决_牛客网 (nowcoder.com )

SELECT emp_no,
    salary,
    SUM(salary) OVER(ORDER BY emp_no) running_total
FROM salaries
WHERE to_date = '9999-01-01'

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

给出employees表中排名为奇数行的first_name牛客题霸牛客网 (nowcoder.com )

题解：题解 | #给出employees表中排名为奇数行#_牛客网 (nowcoder.com )

SELECT first_name
FROM employees JOIN(
    SELECT first_name,
        ROW_NUMBER() OVER(ORDER BY first_name) r_num
    FROM employees
) t USING(first_name)
WHERE r_num % 2 = 1

异常的邮件

异常的邮件概率牛客题霸牛客网 (nowcoder.com )

题解：牛客网 - 找工作神器|笔试题库|面试经验|实习招聘内推，求职就业一站解决_牛客网 (nowcoder.com )

SELECT date,
    ROUND(
        AVG(IF(type='no_completed',1,0))
    ,3) p
FROM email
WHERE send_id IN (
    SELECT id
    FROM user
    WHERE is_blacklist = 0
) AND receive_id IN (
    SELECT id
    FROM user
    WHERE is_blacklist = 0
)
GROUP BY date
ORDER BY date

牛客每个人最近的登录日期（二）

牛客每个人最近的登录日期(二)牛客题霸牛客网 (nowcoder.com )

题解：题解 | #牛客每个人最近的登录日期(二)#_牛客网 (nowcoder.com )

SELECT u.name AS u_n,c.name AS c_n,
    l.date
FROM
    login l
    JOIN user u ON l.user_id = u.id
    JOIN client c ON l.client_id = c.id
WHERE (l.user_id, l.date) IN (
        SELECT user_id,MAX(date)
        FROM login
        GROUP BY user_id
        )
ORDER BY u_n;

牛客每个人最近的登录日期（三）

牛客每个人最近的登录日期(三)牛客题霸牛客网 (nowcoder.com )

题解：题解 | #牛客每个人最近的登录日期(三)#_牛客网 (nowcoder.com )

select round(count(distinct 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);

牛客每个人最近的登录日期（四）

牛客每个人最近的登录日期(四)牛客题霸牛客网 (nowcoder.com )

题解：题解 | #牛客每个人最近的登录日期(四)#_牛客网 (nowcoder.com )

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

牛客每个人最近的登录日期（六）

牛客每个人最近的登录日期(六)牛客题霸牛客网 (nowcoder.com )

题解：题解 | #牛客每个人最近的登录日期(六)#_牛客网 (nowcoder.com )

select u.name as u_n
        ,p.date as date
        ,sum(p.number)over(partition by u.name order by date)as ps_num
from passing_number p,user u
where p.user_id=u.id
order by p.date,u.name;

考试分数（三）

考试分数(三)牛客题霸牛客网 (nowcoder.com )

题解：题解 | #考试分数(三)#_牛客网 (nowcoder.com )

SELECT a.id,l.name,a.score
FROM
    language AS l
    JOIN (
        SELECT id,language_id,score,
            dense_rank() over (PARTITION BY language_id ORDER BY score DESC
            ) AS rank_num
        FROM grade
    ) a ON l.id = a.language_id
WHERE rank_num <= 2
ORDER BY l.name,a.score DESC,a.id;

考试分数（四）

考试分数(四)牛客题霸牛客网 (nowcoder.com )

题解：题解 | #考试分数(四)#_牛客网 (nowcoder.com )

SELECT job,
    FLOOR((COUNT(1) + 1) / 2) start,
    FLOOR(COUNT(1) / 2 + 1) end
FROM grade
GROUP BY job
ORDER BY job
    -- 3个数 start = 2,end = 2
    -- 4个数 statr = 2,end = 3
    -- start = (n + 1) / 2
    -- end = n / 2 + 1

牛客的课程订单分析（四）

牛客的课程订单分析(四)牛客题霸牛客网 (nowcoder.com )

题解：题解 | #牛客的课程订单分析(四)#_牛客网 (nowcoder.com )

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

牛客的课程订单分析（七）

牛客的课程订单分析(七)牛客题霸牛客网 (nowcoder.com )

题解：题解 | #牛客的课程订单分析(七)#_牛客网 (nowcoder.com )

SELECT IF(a.client_id = 0,'GroupBuy',c.name) AS source,COUNT(*) AS cnt
  FROM 
(SELECT client_id
  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 status = 'completed' AND status = 'completed' AND product_name IN ('C++','Java','Python')
                        GROUP BY user_id 
                       HAVING COUNT(*) >= 2 ) ) a
  LEFT JOIN client c ON a.client_id = c.id
 GROUP BY a.client_id
 ORDER BY source

最差是第几名（二）

最差是第几名(二)牛客题霸牛客网 (nowcoder.com )

题解：牛客网 - 找工作神器|笔试题库|面试经验|实习招聘内推，求职就业一站解决_牛客网 (nowcoder.com )

select grade
from (select grade,(
                SELECT sum(number)
                FROM class_grade
            ) as total,
            sum(number) over (order by grade) a,
            sum(number) over (order by grade desc) b
        from class_grade
    ) t1
where a >= total / 2 and b >= total / 2
order by grade;

获得积分最多的人

获得积分最多的人(二)牛客题霸牛客网 (nowcoder.com )

题解：题解 | #获得积分最多的人(二)#_牛客网 (nowcoder.com )

select user.id, user.name,t.grade_sum from(
select user_id,sum(grade_num) as grade_sum from grade_info group by user_id
having grade_sum=(select sum(grade_num) as grade_sum from grade_info group by user_id order by grade_sum desc limit 1) 
) t
join user
on t.user_id=user.id
order by user.id

网易云音乐推荐(网易校招笔试真题)

题解：牛客网 - 找工作神器|笔试题库|面试经验|实习招聘内推，求职就业一站解决_牛客网 (nowcoder.com )

with t1 as (
    SELECT DISTINCT music_name
    FROM follow
    JOIN music_likes 
        on follow.follower_id = music_likes.user_id
    JOIN music
        on music_likes.music_id = music.id
    WHERE follow.user_id = 1 and music_name NOT IN(
    SELECT music_name
    FROM music_likes
        JOIN music
            ON music_likes.music_id = music.id
    WHERE music_likes.user_id = 1
    )
)

SELECT t1.music_name
FROM t1
    JOIN music on t1.music_name = music.music_name
ORDER BY music.id

困难

获取每个部门当前员工薪水最高的相关信息

题解：题解 | #获取每个部门中当前员工薪水最高的相关信息#_牛客网 (nowcoder.com )

SELECT dept_no,emp_no,salary as maxSalary
FROM dept_emp
    JOIN salaries USING(emp_no)
WHERE (dept_no,salary) in (
    SELECT dept_no,max(salary)
    FROM dept_emp
        JOIN salaries USING(emp_no)
    GROUP BY dept_no
)
ORDER BY dept_no

窗口函数做法：
SELECT t.dept_no,t.emp_no,t.salary AS maxSalary
FROM(SELECT d.dept_no,d.emp_no,s.salary,
            DENSE_RANK() OVER (PARTITION BY d.dept_no ORDER BY s.salary DESC) AS sal_rank
    FROM dept_emp d
        JOIN salaries s ON d.emp_no = s.emp_no
    ) t
WHERE t.sal_rank = 1
ORDER BY t.dept_no;

查找职员入职以来的薪水涨幅情况

查找在职员工自入职以来的薪水涨幅情况牛客题霸牛客网 (nowcoder.com )

题解：牛客网 - 找工作神器|笔试题库|面试经验|实习招聘内推，求职就业一站解决_牛客网 (nowcoder.com )

select b.emp_no,(b.salary-a.salary) as growth
from (select e.emp_no,s.salary
    from employees e left join salaries  s on e.emp_no=s.emp_no
    WHERE e.hire_date=s.from_date)a -- 入职工资表
 inner join 
    (select e.emp_no,s.salary
    from employees e left join salaries  s on e.emp_no=s.emp_no
    where s.to_date='9999-01-01')b -- 现在工资表
on a.emp_no=b.emp_no
order by growth

获取员工当前薪水比其manger还高的相关信息

获取员工其当前的薪水比其manager当前薪水还高的相关信息牛客题霸牛客网 (nowcoder.com )

题解：题解 | #获取员工薪水比其manager薪水还高#_牛客网 (nowcoder.com )

SELECT t2.emp_no as emp_no,
    t1.emp_no as manager_no,
    t2.salary as emp_salary,
    t1.salary as manager_salary
FROM (SELECT m.dept_no,m.emp_no,s.salary
            FROM dept_manager m
             JOIN salaries s USING(emp_no)
            WHERE s.to_date = '9999-01-01'
) t1 -- 领导信息
JOIN (SELECT e.dept_no,e.emp_no,s.salary
    FROM dept_emp e
        JOIN salaries s USING(emp_no)
    WHERE (dept_no,emp_no) NOT IN(
        SELECT dept_no,emp_no
        FROM dept_manager
    ) AND s.to_date = '9999-01-01'
) t2 -- 员工信息
on t1.dept_no = t2.dept_no
WHERE t1.salary < t2.salary

汇总各个部门当前员工的title类型的分配数目

汇总各个部门当前员工的title类型的分配数目牛客题霸牛客网 (nowcoder.com )

题解：题解 | #汇总各个部门当前员工的title类型的分配数目#_牛客网 (nowcoder.com )

SELECT dept_no,dept_name,title,count(1) 'count'
FROM dept_emp
    JOIN titles USING(emp_no)
    JOIN departments USING(dept_no)
WHERE dept_emp.to_date = '9999-01-01'
GROUP BY dept_no,title
ORDER BY dept_no,title

牛客每个人最近的登录日期

牛客每个人最近的登录日期(五)牛客题霸牛客网 (nowcoder.com )

题解：题解 | #牛客每个人最近的登录日期(五)#_牛客网 (nowcoder.com )

SELECT a.date,ROUND(
    COUNT(DISTINCT login.user_id)/ COUNT(a.user_id)
    ,3) AS p
FROM (SELECT user_id,MIN(date) AS date 
    FROM login 
    GROUP BY user_id) AS a
LEFT JOIN login
ON login.user_id=a.user_id
AND login.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;

考试分数（五）

考试分数(五)牛客题霸牛客网 (nowcoder.com )

题解：题解 | #考试分数(五)#_牛客网 (nowcoder.com )

SELECT id,job,score,rk_b as t_rank
FROM (SELECT id,job,score,
    ROW_NUMBER()OVER(PARTITION BY job ORDER BY score) rk_a,
    ROW_NUMBER()OVER(PARTITION BY job ORDER BY score DESC) rk_b
FROM grade
) m
JOIN (
    SELECT job,count(1) job_count
    FROM grade
    GROUP BY job
) n
USING (job)
WHERE rk_a >= job_count / 2 AND rk_b >= job_count / 2
ORDER BY id

牛客的课程订单分析（五）

牛客的课程订单分析(五)牛客题霸牛客网 (nowcoder.com )

题解：牛客网 - 找工作神器|笔试题库|面试经验|实习招聘内推，求职就业一站解决_牛客网 (nowcoder.com )

SELECT DISTINCT t3.user_id,t1.first_buy_date,t2.second_buy_date,t3.cnt
FROM(SELECT user_id,date as first_buy_date
    FROM(SELECT user_id,date,
        ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY date) rk
        FROM order_info
        WHERE date > '2025-10-15'
        and product_name in ('C++','Java','Python')
        and status = 'completed'
        ) t
    WHERE rk = 1
) t1
JOIN (
    SELECT user_id,date as second_buy_date
    FROM(SELECT user_id,date,
        ROW_NUMBER()OVER(PARTITION BY user_id ORDER BY date) rk
        FROM order_info
        WHERE date > '2025-10-15'
        and product_name in ('C++','Java','Python')
        and status = 'completed'
        ) t
    WHERE rk = 2
) t2 on t1.user_id = t2.user_id
JOIN (
    SELECT user_id,count(1) cnt
    FROM order_info
    WHERE date > '2025-10-15'
        and product_name in ('C++','Java','Python')
        and status = 'completed'
    GROUP BY user_id
    HAVING count(1) > 1
) t3 ON t1.user_id = t3.user_id
ORDER BY t3.user_id

--开窗函数解法
select
 a.*
from
    (select
     user_id,
     min(date) over(partition by user_id) as first_buy_date,
     lead(date,1,0) over(partition by user_id order by date) as second_buy_date,
     count(*) over(partition by user_id) as cnt
    from order_info
    where date>='2025-10-16'
      and status='completed'
      and product_name in('C++','Java','Python')
    ) a
where a.cnt>=2
group by a.user_id
order by a.user_id ;

实习广场投递简历

实习广场投递简历分析(三)牛客题霸牛客网 (nowcoder.com )

题解：牛客网 - 找工作神器|笔试题库|面试经验|实习招聘内推，求职就业一站解决_牛客网 (nowcoder.com )

SELECT m.job,m.first_year_mon,m.first_year_cnt,
    n.second_year_mon,n.second_year_cnt
FROM (SELECT job,date_format(date,'%Y-%m') first_year_mon,sum(num) first_year_cnt
    FROM resume_info
    WHERE year(date) = 2025
    GROUP BY job,first_year_mon
) m
JOIN (SELECT job,date_format(date,'%Y-%m') second_year_mon,sum(num) second_year_cnt
    FROM resume_info
    WHERE year(date) = 2026
    GROUP BY job,second_year_mon -- 将 first_year_mon 改为 second_year_mon
) n on m.job = n.job
WHERE left(m.first_year_mon,4) = left(n.second_year_mon,4) - 1 -- 年份差1
 AND substr(m.first_year_mon,6,2) = substr(n.second_year_mon,6,2) -- 月份相同
ORDER BY m.first_year_mon DESC,m.job DESC

-- 困惑 把年份、月份判定改成下面这个，牛客输出是对的，但报答案错误
WHERE YEAR(DATE_SUB(n.second_year_mon, INTERVAL 1 YEAR)) = YEAR(m.first_year_mon)
  AND MONTH(n.second_year_mon) = MONTH(m.first_year_mon)
ORDER BY m.first_year_mon DESC,m.job DESC

获取积分最多的人（三）

获得积分最多的人(三)牛客题霸牛客网 (nowcoder.com )

题解：牛客网 - 找工作神器|笔试题库|面试经验|实习招聘内推，求职就业一站解决_牛客网 (nowcoder.com )

with t1 as(
    SELECT user_id,
    sum(
        case WHEN type = 'add' then grade_info.grade_num
            else -grade_info.grade_num end
    ) grade_num
FROM grade_info
GROUP BY user_id
)-- 得到user_id、总的grade_num积分

SELECT t1.user_id,user.name,t1.grade_num
FROM t1
    JOIN user on t1.user_id = user.id
WHERE t1.grade_num = (
    SELECT max(grade_num)
    FROM t1
)
ORDER BY t1.user_id

如果觉得文章对你有用，请随意赞赏

牛客 SQL

牛客SQL-较难篇

http://140.143.171.137//archives/NiukeSQL-1

作者

yingzi

发布于

2023-07-11

更新于

2023-07-11

许可协议

CC BY 4.0

牛客SQL-较难篇

较难

获取当前薪水第二多的员工的emp_no以及其对应的薪水salary

对所有员工的薪水按照salary降序进行1-N排名

获取所有非manager员工当前的薪水情况

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

统计salary的累计和running_total

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

异常的邮件

牛客每个人最近的登录日期（二）

牛客每个人最近的登录日期（三）

牛客每个人最近的登录日期（四）

牛客每个人最近的登录日期（六）

考试分数（三）

考试分数（四）

牛客的课程订单分析（四）

牛客的课程订单分析（七）

最差是第几名（二）

获得积分最多的人

网易云音乐推荐(网易校招笔试真题)

困难

获取每个部门当前员工薪水最高的相关信息

查找职员入职以来的薪水涨幅情况

获取员工当前薪水比其manger还高的相关信息

汇总各个部门当前员工的title类型的分配数目

牛客每个人最近的登录日期

考试分数（五）

牛客的课程订单分析（五）

实习广场投递简历

获取积分最多的人（三）

作者

发布于

更新于

许可协议

评论