牛客SQL

2023-03-30 23:54| 来源: 网络整理| 查看: 265

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

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

题解：题解 | #获取每个部门中当前员工薪水最高的相关信息#_牛客网 (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 牛客每个人最近的登录日期(五)牛客题霸牛客网 (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;

考试分数（五）

="https://www.nowcoder.com/practice/b626ff9e2ad04789954c2132c74c0512?tpId=82&tqId=35496&rp=1&ru=/exam/oj&qru=/exam/oj&sourceUrl=%2Fexam%2Foj%3Fdifficulty%3D5%26page%3D1%26pageSize%3D50%26search%3D%26tab%3DSQL%E7%AF%87%26topicId%3D82&difficulty=5&judgeStatus=undefined&tags=&title=">考试分数(五)牛客题霸牛客网 (http://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

今日新闻

推荐新闻