牛客网SQL刷题解法记录

#解法一 窗口函数

select emp_no,birth_date,first_name,last_name,gender,hire_date 
from
(select *,dense_rank()over(order by hire_date desc) rk from employees)x1
where rk=3

#解法二 子查询 distinct+limit

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

【知识点】limit m,n 表示找到第m+1条开始的n条记录。（只有一个参数时，limit n 表示找到排序之后的前n条）

---------------------------------------------------------------------------------------------------------------------------------

SQL204 获取所有非manager的员工emp_no

获取所有非manager的员工emp_no_牛客题霸_牛客网 (nowcoder.com)

方法1：NOT IN+子查询
select emp_no
from employees
where emp_no not in (select emp_no from dept_manager);


方法2：LEFT JOIN左连接+IS NULL
SELECT e.emp_no
FROM employees AS e
LEFT JOIN dept_manager AS d
ON e.emp_no=d.emp_no
WHERE dept_no IS NULL;

---------------------------------------------------------------------------------------------------------------------------------

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

一、max()+子查询
select dept_no,d.emp_no,salary maxSalary
from dept_emp d
inner join salaries s
on d.emp_no=s.emp_no
where d.to_date='9999-01-01' and s.to_date='9999-01-01' and (dept_no,salary) 
in (select dept_no,max(salary)from dept_emp d inner join salaries s
on d.emp_no=s.emp_no where d.to_date='9999-01-01' and s.to_date='9999-01-01' group by dept_no)
order by dept_no

注意筛选时可以两列一起筛选



二、max()+表连接
select x1.dept_no ,x2.emp_no,x1.maxSalary from
(select dept_no,max(salary) maxSalary from dept_emp d inner join salaries s on d.emp_no=s.emp_no
where d.to_date='9999-01-01' and s.to_date='9999-01-01'
group by dept_no)x1
inner join 
(select dept_no,d.emp_no,Salary
from dept_emp d
inner join salaries s
on d.emp_no=s.emp_no
where d.to_date='9999-01-01' and s.to_date='9999-01-01')x2
on x1.maxSalary=x2.Salary and x1.dept_no=x2.dept_no

难点：各表名字容易写混乱，导致有问题查询不出
总结：两个子查询各自的表不怕其中select有对应重名，在最后链接时写清楚x1.xx=x2.xx即可，主查询x1.xx时不用改名字，会自动变为xx，又或者可以等主查询时再改名



三、rank()over()窗口函数
select dept_no,emp_no,maxSalary 
from (select dept_no,d.emp_no,salary maxSalary,rank()over(partition by dept_no order by salary desc) rk from dept_emp d
inner join salaries s
on d.emp_no=s.emp_no
where d.to_date='9999-01-01' and s.to_date='9999-01-01')x1
where rk=1
order by dept_no

子查询若用select * ,会导致主查询emp_no列出现"Duplicate column name 'emp_no'"的错误，难写清楚是子查询中的哪一列，为此一般不用*而是子查询先详细写出来所需要的列



四、max()over()窗口函数
select dept_no,emp_no,maxSalary from (select dept_no,d.emp_no emp_no,salary maxSalary,max(salary)over(partition by dept_no) sk
from dept_emp d
inner join salaries s
on d.emp_no=s.emp_no
where d.to_date='9999-01-01' and s.to_date='9999-01-01')xb
where sk=maxSalary
order by dept_no

注意用法（与rank窗口函数）的区别

求最高可用窗口函数，用聚合函数的话可利用表连接，或者子查询条件筛选

--------------------------------------------------------------------------------------------------------------------------------

SQL209 查找employees表emp_no与last_name的员工信息

查找employees表emp_no与last_name的员_牛客题霸_牛客网 (nowcoder.com)

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

【知识点】：求奇偶数

方法一：使用mod()

mod(a,b) 在sql中的意思是 a / b 的余数
mod(id, 2)=1 是指id是奇数。
mod(id, 2)=0 是指id是偶数。

方法二：使用%

如id是奇数：(id % 2) = 1;
id是偶数：(id % 2) = 0;

---------------------------------------------------------------------------------------------------------------------------------

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

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

窗口函数：
select emp_no,salary 
from (select *,rank()over(order by salary desc)rk from salaries where to_date='9999-01-01') xb
where rk=2
order by
emp_no 

子查询+limit
select emp_no, 
salary from salaries
where to_date='9999-01-01' and salary=(select salary from salaries
where to_date='9999-01-01'
group by salary
order by salary desc
limit 1,1)

求非分组的第n可以用窗口函数，或用distinct/group by ＋limit m,n锁定数值再表连接，或者子查询条件筛选

注意：MySQL中in关键字不能和limit连用，即会报1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'的错误

不让in关键字和limit关键字在一层上，把后面的子查询语句包起来即可解决

---------------------------------------------------------------------------------------------------------------------------------

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

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

题目要求：不能使用order by完成

一、表自连接+count()解法：

select e.emp_no,s.salary,e.last_name,e.first_name
from employees e
join 
salaries s on e.emp_no=s.emp_no 
and  s.to_date='9999-01-01'
and s.salary in 
(select 
s.salary from salaries s left join salaries s1 
on s.salary<=s1.salary
where s.to_date='9999-01-01' and s1.to_date='9999-01-01'
group by s.salary 
having count(s1.salary)=2)

二、max()解法：

select e.emp_no, salary,last_name,first_name 
from employees e 
inner join salaries s 
on e.emp_no=s.emp_no
where s. to_date='9999-01-01' and salary=(select max(salary) from salaries
where to_date='9999-01-01' and salary not in (select max(salary) from salaries
where to_date='9999-01-01'))

---------------------------------------------------------------------------------------------------------------------------------

SQL215 查找在职员工自入职以来的薪水涨幅情况

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

表链接解法：

select x1.emp_no,(x2.salary-x1.salary)growth 
from
(select e.emp_no,salary 
from employees e
inner join salaries s
on e.emp_no=s.emp_no and e.hire_date=s.from_date)x1
inner join
(select e.emp_no,salary from employees e
inner join salaries s
on e.emp_no=s.emp_no
where to_date='9999-01-01')x2
on x1.emp_no =x2.emp_no
order by growth

精细写法：

select a.emp_no, (b.salary - c.salary) as growth
from
    employees as a
    inner join salaries as b
    on a.emp_no = b.emp_no and b.to_date = '9999-01-01'
    inner join salaries as c
    on a.emp_no = c.emp_no and a.hire_date = c.from_date
order by growth asc

--------------------------------------------------------------------------------------------------------------------------------

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

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

一、仅用表连接：
select de.dept_no,e.emp_no,salary 
from employees e
left join dept_emp de
on e.emp_no=de.emp_no
inner join dept_manager dm
on de.dept_no =dm.dept_no and de.emp_no!=dm.emp_no
inner join salaries s
on e.emp_no=s.emp_no 
where dm.to_date='9999-01-01' and de.to_date='9999-01-01' and s.to_date='9999-01-01'


二、表连接+where 子查询筛选
select b.dept_no,a.emp_no,salary 
from employees a 
left join dept_emp b 
on a.emp_no= b.emp_no 
inner join salaries c 
on a.emp_no=c.emp_no
where b.to_date='9999-01-01' and c.to_date='9999-01-01' and a.emp_no not in (select emp_no from dept_manager where to_date='9999-01-01')

---------------------------------------------------------------------------------------------------------------------------------

SQL233 针对上面的salaries表emp_no字段创建索引idx_emp_no

针对上面的salaries表emp_no字段创建索引idx__牛客题霸_牛客网 (nowcoder.com)

select *
from salaries
force index (idx_emp_no)
where emp_no=10005

【知识点】：强制索引FORCE INDEX

FORCE INDEX强制查询优化器使用指定的命名索引。查询优化器是MySQL数据库服务器中的一个组件，它为SQL语句提供最佳的执行计划。查询优化器使用可用的统计信息来提出所有候选计划中成本最低的计划。
书写顺序;

复制代码

SELECT……

FROM ……

FORCE INDEX(index_name)

WHERE……

--------------------------------------------------------------------------------------------------------------------------------

SQL235 构造一个触发器audit_log

构造一个触发器audit_log_牛客题霸_牛客网 (nowcoder.com)

create trigger audit_log
after insert on employees_test
for each row
begin
insert into audit values(new.id,new.name);
end;

语法：
create trigger triggerName      
    after/before/                          // 触发时间 trigger_time
    insert/update/delete                  // 监视事件 trigger_event
    on table_name                        // 监视地点 table
    for each row                        // 这句话在mysql中是固定的
    begin
    
    sql语句(insert/update/delete);     // 触发事件  trigger_stmt 注意这里要有分号
   
    end;

---------------------------------------------------------------------------------------------------------------------------------

SQL236 删除emp_no重复的记录，只保留最小的id对应的记录

删除emp_no重复的记录，只保留最小的id对应的记录。_牛客题霸_牛客网 (nowcoder.com)

错误方法）
DELETE FROM titles_test
WHERE id NOT IN(
    SELECT MIN(id)
    FROM titles_test
    GROUP BY emp_no);
MySQL中不允许在子查询的同时删除表数据（不能一边查一边把查的表删了）

!!正确方法）

DELETE FROM titles_test
WHERE id NOT IN(
    SELECT * FROM(
    SELECT MIN(id)
    FROM titles_test
    GROUP BY emp_no)a);  -- 把得出的表重命名那就不是原表了

--------------------------------------------------------------------------------------------------------------------------------

SQL238 将id=5以及emp_no=10001的行数据替换成id=5以及emp_no=1000510005,其他数据保持不变，使用replace实现，直接使用update会报错

将id=5以及emp_no=10001的行数据替换成id=5_牛客题霸_牛客网 (nowcoder.com)

1、使用replace
UPDATE titles_test
SET emp_no = REPLACE(emp_no, 10001, 10005)
WHERE id = 5;


2、使用insert(不符合题意）
有遇到重复主键了就进行更新emp_no的值

INSERT INTO titles_test
    VALUES(5, 10001 ,'Senior Engineer', '1986-06-26', '9999-01-01')
    ON DUPLICATE KEY UPDATE emp_no = 10005;


3、使用replace into
REPLACE INTO当遇到primary 或者 unique key 的时候，会首先进行update

REPLACE INTO titles_test
    VALUES(5, 10005 ,'Senior Engineer', '1986-06-26', '9999-01-01') ;

【知识点】：mysql中常用的三种插入数据的语句:

insert into：正常的插入数据，插入数据的时候会检查主键或者唯一索引，如果出现重复就会报错；
replace into：表示插入并替换数据，若表中有primary key或者unique索引，在插入数据的时候，若遇到重复的数据，则用新数据替换，如果没有数据效果则和insert into一样；
insert ignore into：插入并忽略数据，如果中已经存在相同的记录，则忽略当前新数据。这样不用校验是否存在了，有则忽略，无则添加

Mysql 之插入数据（insert into 、 replace into 解析）_replace into和insert into-CSDN博客

--------------------------------------------------------------------------------------------------------------------------------

SQL245 查找字符串中逗号出现的次数

查找字符串中逗号出现的次数_牛客题霸_牛客网 (nowcoder.com)

select id,length(string)-length(replace(string,',','')) cnt from strings

【知识点】：求字符串长度 length 函数

1.使用length()函数获取字符串长度；用replace()函数将","替换成" "(英文逗号替换成空格)；

2.逆向思维，将原字符串长度 - 替换逗号后的字符串长度（空格不算字符串）=逗号的个数；

3.记得列的别名。

---------------------------------------------------------------------------------------------------------------------------------

SQL246 获取employees中的first_name

获取employees中的first_name_牛客题霸_牛客网 (nowcoder.com)

select first_name from employees
order by substring(first_name,-2) ;

【知识点】：字符串函数

MySQL函数之字符串函数_mysql包含字符串函数-CSDN博客

---------------------------------------------------------------------------------------------------------------------------------

SQL247 按照dept_no进行汇总

按照dept_no进行汇总_牛客题霸_牛客网 (nowcoder.com)

select dept_no,group_concat(emp_no order by emp_no) employees
from dept_emp
group by dept_no

【知识点】：分组连接字符串函数 group_concat

一、group_concat函数的功能
　　将group by产生的同一个分组中的值连接起来，返回一个字符串结果。

group_concat([distinct] 字段名 [order by 排序字段 asc/desc] [separator '分隔符'])
　　说明：

　　（1）使用distinct可以排除重复值；

　　（2）如果需要对结果中的值进行排序，可以使用order by子句；

　　（3）separator是一个字符串值，默认为逗号。

此函数必须与GROUP BY配合使用

---------------------------------------------------------------------------------------------------------------------------------

SQL248 平均工资

平均工资_牛客题霸_牛客网 (nowcoder.com)

解法一 where+子查询筛选
select avg(salary)
from salaries
where salary not in (select
min(salary)
from salaries
where to_date = '9999-01-01')and salary not in (
select
max(salary)
from salaries
where to_date = '9999-01-01')
and to_date = '9999-01-01'

#where不能用聚合函数 因此用子查询 排除最大值和最小值
简单粗暴

解法二 排名窗口函数
select
avg(salary)
from (select*
,row_number() over(order by salary) r1
,row_number() over(order by salary desc) r2
from salaries
where to_date = '9999-01-01') a
where a.r1 <>1
and a.r2 <> 1
#把最大值和最小值去掉

！！解法三 子查询+最大值，最小值出口函数
select
avg(salary)
from (select salary
,max(salary) over() s1
,min(salary) over() s2
from salaries
where to_date = "9999-01-01"
) a
where a.salary < a.s1
and a.salary > a.s2

值得注意的点：

一、解法三必须使用窗口函数，因为直接使用max或者min只输出一个观测值，除非使用join将所有观测和max与min连接起来，那样会很麻烦，而窗口函数则直接输出全部值

--------------------------------------------------------------------------------------------------------------------------------

SQL251 使用含有关键字exists查找未分配具体部门的员工的所有信息

使用含有关键字exists查找未分配具体部门的员工的所有信息_牛客题霸_牛客网 (nowcoder.com)

exists 解法：

select *
from employees
where not exists
(
    select emp_no
    from dept_emp
    where employees.emp_no=dept_emp.emp_no
)

not exists可以理解成取非。
如果括号里有值返回，那就是真；再取一个not，那就变成假，就不选这一条数据。



in 解法（不符题意）：

select *
from employees
where emp_no not in
(
    select emp_no
    from dept_emp
)

【知识点】：exists 的用法

EXISTS语句：执行employees.length次
指定一个子查询，检测行的存在。遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。匹配上就将结果放入结果集中。

exists是先从主查询中取得一条数据，再代入到子查询中，执行一次子查询，判断子查询是否能返回结果，主查询有多少条数据，子查询就要执行多少次

IN 语句：只执行一次
确定给定的值是否与子查询或列表中的值相匹配。in在查询的时候，首先查询子查询的表，然后将内表和外表做一个笛卡尔积，然后按照条件进行筛选。所以相对内表比较小的时候，in的速度较快。

in是先执行子查询，得到一个结果集，将结果集代入外层谓词条件执行主查询，子查询只需要执行一次

Sql 语句中 IN 和 EXISTS 的区别及应用_数据库in和exists的区别-CSDN博客

--------------------------------------------------------------------------------------------------------------------------------

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

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

where+子查询：

select first_name 
from employees
where first_name in
(
    select first_name from
    (
        select first_name,rank()over(order by first_name)rk 
    from employees
    )a
    where rk%2=1)


表自连接：

select e1.first_name 
from employees e1 
LEFT join employees e2 on e1.first_name>=e2.first_name
group by e1.first_name
having count(e2.first_name)%2=1

【知识点】：窗口函数会改变表格顺序，故而需要通过筛选条件或者表连接确保原表位置不变。

---------------------------------------------------------------------------------------------------------------------------------

SQL257 刷题通过的题目排名

刷题通过的题目排名_牛客题霸_牛客网 (nowcoder.com)

自连接+count进行排名：
select a.id,a.number,count(distinct b.number)t_rank 
from passing_number a
left join passing_number b
on a.number<=b.number
group by a.id,a.number
order by t_rank,id


dense_rank(窗口函数：
select id,number,dense_rank()over(order by number desc) t_rank
from passing_number

【知识点】：1、注意count函数里面可以使用distinct，且在本题一定要用distinct才能把排名边连贯

2、此题若使用count（）over（）函数--rk，会出现无法 group by rk，比较麻烦

--------------------------------------------------------------------------------------------------------------------------------

SQL259 异常的邮件概率

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

select date,round(count(case when type='no_completed' then 1 else null end)/count(type),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

注明：count 里面可以用条件函数

--------------------------------------------------------------------------------------------------------------------------------

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

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

rank窗口函数：
select u_n,c_n,date 
from (select b.name u_n
,c.name c_n
,date
,rank()over(partition by user_id order by date desc) rk
from login a 
inner join user b
on a.user_id=b.id
inner join client c
on a.client_id=c.id)xb
where rk=1
order by u_n

此表无需分组，只是从本表筛选出符合条件的行，
所以rank窗口函数可规避用group up造成主查询后面select 不好写字段的问题



select b.name u_n
,c.name c_n
,date 
from login a 
inner join user b
on a.user_id=b.id
inner join client c
on a.client_id=c.id
where (user_id,date) in
(select user_id,max(date) from login
group by user_id)
order by u_n

运用多列查询，在子查询中把max（date）查出来

--------------------------------------------------------------------------------------------------------------------------------

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

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

自连接解法：
select round(count(distinct c.user_id)/count(distinct b.user_id),3) as p from
(select user_id,min(date) as date from login
group by user_id) b
left join (select user_id,date from login) c
on c.date=date_add(b.date,INTERVAL 1 DAY) and c.user_id=b.user_id	



更简洁的解法：
select 
round(count(distinct user_id)*1.0/(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);

--------------------------------------------------------------------------------------------------------------------------------

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

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

解法一、自连接：
select a.date
,ifnull(count(distinct b.user_id),0) new 
from (select distinct date from login) a
  left join 
  (select user_id,min(date) as date from login
group by user_id)b
on a.date=b.date
group by a.date
order by a.date

注：常规思路，筛选符合条件的表格进行自连接



解法二：窗口函数+count+条件函数

select 
date,count(distinct if(rk=1,user_id,null)) new 
from
(
    select user_id,date,rank()over(partition by user_id order by date) rk 
    from login 
    )a
group by date
order by date

注：不用自连接，直接在大表后面标注出符合条件的行，再利用条件函数加count进行计数
如果用 select date,sum(if(rk=1,1,0)) new from 会出现无法用distinct 把第一次登录那天用户登录了两次的情况按一次计数。所以用count妥当些。


解法三：count+条件函数
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;

注：直接在条件函数里面筛选出符合条件的行，进行计数

注：计数类型的题，可以往直接在条件函数里面进行筛选的方向思考

---------------------------------------------------------------------------------------------------------------------------------

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

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

方法1.1：在count 里面使用条件函数
select date
,ifnull(round(
    count(distinct
    case when (user_id,date) 
    in (select user_id,min(date) from login group by user_id)
    and (user_id,date_add(date,INTERVAL 1 day)) in (select user_id,date from login)
 then user_id else null end)
/
    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)
,3),0) as p
from login
group by date
order by date 



方法1.2：在sum里面使用条件函数
SELECT date
,IFNULL(ROUND(SUM(
        CASE WHEN (user_id,date) 
        IN (SELECT user_id,DATE_ADD(date,INTERVAL -1 DAY) FROM login)
        AND (user_id,date) IN (SELECT user_id,MIN(date) 
        FROM login GROUP BY user_id)
 THEN 1 ELSE 0 END)
/
  SUM(CASE WHEN (user_id,date) 
     IN (SELECT user_id,MIN(date) 
     FROM login GROUP BY user_id)
THEN 1 ELSE 0 END),3),0) AS p
FROM login
GROUP BY date
ORDER BY date;



方法2：使用自连接方式（漏斗模型）
select a.date ,ifnull(round(count(distinct c.user_id)/count(distinct b.user_id),3),0) p
from login a
left join (select user_id,MIN(date) as date FROM login GROUP BY user_id)b
on a.date=b.date and a.user_id=b.user_id
left join (SELECT user_id,date FROM login) c
on date_add(b.date,INTERVAL 1 DAY)=c.date and  b.user_id=c.user_id
group by a.date

1、方法1.1 里面加上了distinct,故而case when 里面统计的应该是user_id，若跟count一样把统计的设置为1，就会出现答案错误。这样可以规避同个用户在同一天登录2次以上的情况（可对比方法1.2）

2、聚合行数会跳过null，故而使用ifnull 函数归整答案

3、因为分子的date需要与分母的date保持一致，故而分子的条件函数有两种写法，一、一种是日期是首次登录日期，且其隔天存在大表里（1.1）二、另一种是日期是首次登录日期，且其是大表某行的前一天，由此确定两天都有登录（1.2）

--------------------------------------------------------------------------------------------------------------------------------

SQL269 考试分数(四)

sql: rank/row_number/count/sum/avg/max/min over的用法_SUMMERENT的博客-CSDN博客

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

【知识点】：ceil、floor函数

向上取整函数：ceil(x)

返回x的向上取整的整数

向下取整函数:floor(x)

返回x的向下取整的整数

【知识点】：求中位数是第几位

区间：总数为奇数floor((count(id)+1)/2)

总数为偶数floor((count(id)+1)/2) ,floor((count(id)+2)/2）

---------------------------------------------------------------------------------------------------------------------------------

SQL270 考试分数(五)

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


解法一、常规做法：两表连接
select xb2.id,xb2.job,xb2.score,t_rank from
(select job,floor((count(id)+1)/2) start,floor((count(id)+2)/2) end
from grade
group by job)xb1
inner join 
(select *,row_number()over(partition by job order by score desc,id desc) t_rank from grade)xb2
on xb1.job=xb2.job 
where start=t_rank or  end=t_rank 
order by xb2.id


解法二、
select id,job,score,t_rank from
(select *,row_number()over(partition by job order by score desc,id desc) t_rank,floor((count(*) over(partition by job)+1)/2)rk1,floor((count(*) over(partition by job)+2)/2)rk2 from grade)xb
where t_rank=rk1 or t_rank=rk2
order by id

把三列数据全怼到一个表进行筛选，注意count也用窗口函数避免group by。
以上两个解法所用到的定位中位数方法：floor((count(id)+1)/2)，start,floor((count(id)+2)/2)



！！解法三、
select id,job,score,s_rank
from 
(select *
        ,(row_number()over(partition by job order by score desc,id desc))as s_rank
        ,(count(score)over(partition by job))as num
        from grade)t1
where abs(t1.s_rank-(t1.num+1)/2)<1
order by id;

无论奇偶，中位数的位置距离（个数+1）/2 小于1。所以利用绝对函数 abs+公式(t1.s_rank-(t1.num+1)/2)<1可实现寻找各中位数

 
！！解法四、
select id,job,score,b
from (
    select *,
       count(score) over(partition by job) as total,
       row_number() over(partition by job order by score,id) as a, #升序序号
       row_number() over(partition by job order by score desc,id desc) as b #逆序序号
    from grade
) t_rank
where a>=total/2 and b>=total/2
order by id

中位数，即无论升序还是倒序都处于中间的数。分别正序+倒序后，进行a>=total/2 and b>=total/2 可筛选出中位数

【知识点】：求中位数的各种方法

相同分数的 row_number 随机排序一个job多人分数相同的话,会导致序号不统一，所以根据题的需要，要在row_number窗口函数里面加上 id desc。

--------------------------------------------------------------------------------------------------------------------------------

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

牛客的课程订单分析(五)_牛客博客 (nowcoder.net)

解法一（三种窗口函数）
select
    user_id,
    date first_buy_date,
    date1 second_buy_date,	
    cnt 
    from
 (select *,count(*)over(partition by user_id) cnt,rank()over(partition by user_id order by date)rk ,lead(date, 1) OVER (PARTITION BY user_id ORDER BY date) as date1
 from order_info
    where product_name in ('C++', 'Java', 'Python')
    and date > '2025-10-15'
    and status = 'completed')g
    where cnt>=2 and rk=1
    order by user_id

解法二（row_number、lead窗口函数+聚合函数）

select user_id,
    min(date) as first_buy_date,
    min(下一个日期) as second_buy_date,
    max(次数) as cnt
from (
    select *,
        row_number() over(partition by user_id order by date) as 次数,
        lead(date,1) over(partition by user_id order by date) as 下一个日期
    from order_info
    where date>'2025-10-15'
    and status='completed'
    and product_name in ('Python','Java','C++')
    order by user_id) as t
group by user_id
having count(*)>=2
order by user_id;


解法三（row_number、count窗口函数+聚合函数）

题目仅要求返回第1、2次购买时间，故只需返回前两条记录，时间最小为第1次，时间最大为第2次，购买次数可用开窗函数计算，代码如下：

select
 a.user_id,
 min(a.date) as first_buy_date,
 max(a.date) as second_buy_date,
 a.cnt
from
    (select
     user_id,
     date,
     row_number() over(partition by user_id order by date) as rank_no,     #日期排序、标记
     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.rank_no<=2 and a.cnt>=2
group by a.user_id,a.cnt
order by a.user_id ;

解法四（row_number、count窗口函数+聚合、条件函数）
仅返回第1、2次购买时间属于特例，泛用的写法可在min/max内嵌套if/iif/case...when，代码如下：

select
 a.user_id,
 max(case when a.rank_no=1 then a.date else 0 end) as first_buy_date,
 max(case when a.rank_no=2 then a.date else 0 end) as second_buy_date,
 a.cnt
from
    (select
     user_id,
     date,
     row_number() over(partition by user_id order by date) as rank_no,
     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,a.cnt
order by a.user_id ;

【知识点】：如何新建列、灵活运用窗口函数

1、表连接（有很多条件限制时有点冗繁）2、写函数（窗口函数、聚合函数、条件函数）

2、窗口函数的同一查询语句里面不能有group by

--------------------------------------------------------------------------------------------------------------------------------

SQL276 牛客的课程订单分析(六)

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


select xb.id
,is_group_buy
,c.name client_name
from (
select *,count(	product_name)over(partition by user_id)ct
from order_info
where date>'2025-10-15' and status='completed' and product_name in ('C++','Java','Python')
)xb
left join client c
on c.id=xb.client_id
where ct>1
order by xb.id

思路：最终结果表需要ID,拼团信息等不能group by 的，相当于在总表筛选，又需要筛选出符合条件的订单数超过2单的，所以需要在子查询里面进行窗口函数

left join 没有符合的匹配行时会出现null值，刚好满足题干要求，故而本题不用在client_name 这里添加条件函数

---------------------------------------------------------------------------------------------------------------------------------

SQL277 牛客的课程订单分析(七)

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

select 
ifnull(c.name,'GroupBuy') source
,count(xb.id) cnt
from (
select *,count(	product_name)over(partition by user_id)ct
from order_info
where date>'2025-10-15' and status='completed' and product_name in ('C++','Java','Python')
)xb
left join client c
on c.id=xb.client_id
where ct>1
group by source
order by source

--------------------------------------------------------------------------------------------------------------------------------

SQL280 实习广场投递简历分析(三)

select b1.job
,first_year_mon
,first_year_cnt
,second_year_mon
,second_year_cnt
from
(select job,substring(date,1,7)first_year_mon,sum(num) first_year_cnt
from resume_info
where year(date)=2025
group by first_year_mon,job
order by first_year_mon desc,first_year_cnt desc)b1
inner join 
(select job,substring(date,1,7) second_year_mon,sum(num) second_year_cnt
from resume_info
where year(date)=2026
group by second_year_mon,job
order by second_year_mon desc,second_year_cnt desc)b2
on b1.job=b2.job 
and right(first_year_mon,2)=right(second_year_mon,2)
order by first_year_mon desc,b1.job desc


子查询中的 substring(date,1,7)可以改成 date_format(date,'%Y-%m')

【知识点】：常用函数 DATE_FORMAT、left、substring

DATE_FORMAT(d,f) 按表达式f的要求显示日期 d.

如f的表达式'%Y-%m-%d %r' 是2011-11-11 11:11:11 AM。 '%Y-%m‘是2011-11.

注明：DATE_FORMAT转化出来的日期已经变成字符串，无法再以日期形式使用日期函数

LEFT(s,n) 返回字符串 s 的前 n 个字符left

substring（被截取字段，从第几位开始截取，截取长度）

【知识点】：筛选出某一年份的写法:

like '2025%'

或year(date) = 2025

或between '2025-01-01' and '2025-12-31' (不适用于带时间的日期，最后一天容易出错)

或date >= '2025-01-01' and date <= '2025-12-31'

---------------------------------------------------------------------------------------------------------------------------------

SQL282 最差是第几名(二)

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


select grade from
(select grade
,(sum(number)over(order by grade)-number)+1 q1  #确定每个等级的最高名次
,sum(number)over(order by grade) q2  #确定每个等级的最低名次
,sum(number)over() zs  #顺便在同个表格确定好总人数（避免用GROUP BY）
from class_grade) x1
where floor((zs+1)/2) between q1 
and q2 or floor((zs+2)/2) between q1 and q2 #确定好中位数并且进行筛选
order by grade


妙解：

select grade from
(select grade,number,sum(number)over(order by grade)t_rank,sum(number)over(order by grade desc)t_rank1,sum(number)over()as zs
from class_grade)xb
where t_rank>=(zs/2) and t_rank1>=(zs/2)
order by grade

--------------------------------------------------------------------------------------------------------------------------------

SQL285 获得积分最多的人(三)

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

select id,name,grade_num from
(select id,name,grade_num,rank()over(order by grade_num desc) rk from 
(select id,name,sum(if(type='add',grade_num,-grade_num))grade_num from grade_info a inner join user b on a.user_id=b.id group by id,name)xb1)xb2
where rk=1
order by id

思路：先链接以及统计出各个用户的积分，再rank 排名筛选出第一名。

--------------------------------------------------------------------------------------------------------------------------------

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

select c.music_name
from follow a
inner join music_likes b
on a.follower_id=b.user_id
inner join music c
on b.music_id=c.id
where a.user_id=1 
and music_id not in 
(
    select music_id from music_likes where user_id=1
    ) 
group by c.music_name,c.id
order by c.id

【知识点】： distinct和order by 一起用时，order by的字段必须在select中,所以有时间比较绕时可以考虑用group

原因：
1、首先，在MySQL中 distinct 的执行顺序高于 order by
2、第二，distinct 执行时会对查询的记录进行去重，产生一张虚拟的临时表
3、第三，order by 执行时对查询的虚拟临时表进行排序，产生新的虚拟临时表
因为order by 的字段不在select中，SQL语句执行distinct要优先于order by，distinct 去重之后的虚拟临时表中没有 order by后面的字段，所以在执行order by时会报错，所以 distinct 和order by 一起用时，order by的字段必须在select中。
---------------------------------------------------------------------------------------------------------------------------------

SQL288 今天的刷题量(一)

select 
b.name
,count(create_time) cnt
from submission a 
inner join subject b on
a.subject_id=b.id
where create_time=current_date
group by b.name,subject_id
order by cnt desc,subject_id