牛客网SQL编程题-更新到93题

第一题

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

select * from employees order by hire_date desc limit 1 offset 0 
#按入职日期降序排列，取最大值，适用于每个员工入职时间不同
select * from employees where hire_date = (select max(hire_date) from employees
#选择入职时间为最晚的员工，时间越晚数值越大，适用于任何情况

第二题

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

select * from employees order by hire_date DESC limit 1 offset 2
#同上题，注意剪切时从n-1开始
select * from employees where hire_date = 
(select distinct hire_date from employees order by hire_date DESC limit 1 offset 2)
#同上题，适用于入职时间不一定相同情况。其中distinct为查找不同的值

第三题

查找各个部门当前领导的薪水详情以及其对应部门编号dept_no，输出结果以salaries.emp_no升序排序，并且请注意输出结果里面dept_no列是最后一列

select salaries.emp_no, salaries.salary, salaries.from_date, salaries.to_date, dept_manager.dept_no
from dept_manager, salaries
where dept_manager.emp_no=salaries.emp_no
and dept_manager.to_date='9999-01-01'
and salaries.to_date='9999-01-01'
order by salaries.emp_no
/*由于可能存在离职等情况，需加入
“dept_manager.to_date='9999-01-01'”两项*/
#运行出的结果与题中有差异，需继续讨论,好像是题答案里数据有问题
select salaries.emp_no, salaries.salary, salaries.from_date, salaries.to_date,dept_manager.dept_no
from salaries inner join dept_manager
on salaries.emp_no=dept_manager.emp_no
where salaries.to_date='9999-01-01' and dept_manager.to_date='9999-01-01'
#使用内联结，即保留并集

第四题

请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，未分配的部门的员工不显示

select employees.last_name,employees.first_name,dept_emp.dept_no
from dept_emp inner join employees
on dept_emp.emp_no=employees.emp_no
#内联结，取两个表的交集

第四题

查找所有已经分配部门的员工的last_name和first_name以及dept_no，也包括暂时没有分配具体部门的员工

select employees.last_name,employees.first_name,dept_emp.dept_no
from employees left outer join dept_emp
on employees.emp_no=dept_emp.emp_no
#左联结。外联结可以其中部分数据为空，内联结必须两个表均有数据

第七题（五六丢了）

查找薪水记录超过15次的员工号emp_no以及其对应的记录次数t

select emp_no,count(emp_no) as t from salaries
group by emp_no having t>15
#计数，设为新的变量。按某变量分组，分组条件为having不是where

第八题

找出所有员工具体的薪水salary情况，对于相同的薪水只显示一次,并按照逆序显示

select distinct salary from salaries
where to_date='9999-01-01' order by salary DESC;
#方法一，distinct只显示一次
select salary from salaries where to_date='9999-01-01'
group by salary order by salary DESC
#方法二，根据salary分组

第十题（九题没了）

找出所有非部门领导的员工emp_no

select employees.emp_no from employees left join dept_manager
on employees.emp_no=dept_manager.emp_no
where dept_no is null
#方法一，左外联结，再设仅存于领导表中的某变量为空，LEFT JOIN+IS NULL
select emp_no from employees where emp_no not in 
(select emp_no from dept_manager)
#方法二，使用NOT IN+子查询

第十一题

获取所有的员工和员工对应的经理，如果员工本身是经理的话则不显示

select dept_emp.emp_no as emp_no,dept_manager.emp_no as manager from
dept_emp join dept_manager on dept_emp.dept_no=dept_manager.dept_no
where dept_emp.emp_no!=dept_manager.emp_no
#内连接+！=

第十二题

获取每个部门中当前员工薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门编号升序排列

select dept_emp.dept_no as dept_no，
dept_emp.emp_no as emp_no,salaries.salary as salary
from dept_emp join salaries on
dept_emp.emp_no=salaries.emp_no
group by dept_no order by salary DESC limit 1 offset 0
#先在联结表中查找，然后分组，然后排序（降序），最后剪切
/*注意不能用having，HAVING语句用来与聚合函数联合使用，
过滤GROUP BY语句返回的记录集，筛选的是分组，
例如HAVING AVG(score)>=80，筛选的是那些平均成绩大于80的分组，
而上面的HAVING salary=MAX(s.salary)实际上是想筛选分组中的记录。
这是错误的；*/
select r.dept_no,ss.emp_no,r.maxSalary from 
(select d.dept_no,max(s.salary)as maxSalary from dept_emp d,salaries s
where d.emp_no=s.emp_no
and d.to_date='9999-01-01' 
and s.to_date='9999-01-01'
group by d.dept_no)as r,
salaries ss,dept_emp dd
where r.maxSalary=ss.salary
and r.dept_no=dd.dept_no
and dd.emp_no=ss.emp_no
order by r.dept_no asc
/#这是为了避免当一个部门有多个最高值时

第十四题（十三题没了）

查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

select * from employees 
where emp_no%2=1 and last_name not like 'Mary'
order by hire_date DESC

第十五题

请你查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列

select * from employees 
where emp_no%2=1 and last_name not like 'Mary'
order by hire_date DESC

第十六题

统计出各个title类型对应的员工薪水对应的平均工资avg。结果给出title以及平均工资avg，并且以avg升序排序

select t.title,avg(s.salary) from titles as t join salaries as s
on t.emp_no=s.emp_no
group by t.title order by avg(s.salary) ASC

第十七题

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

select emp_no,salary from salaries
where salary=(select salary from salaries order by
salary DESC limit 1 offset 1)
#为了避免有人并列第二多，先查找薪水第二多的值

第十八题

查找薪水排名第二多的员工编号emp_no、薪水salary、last_name以及first_name，不能使用order by完成

select e.emp_no,s.salary,e.last_name,e.first_name from
employees as e join salaries as s on e.emp_no=s.emp_no
where s.salary=(
select max(salary) from salaries
where salary<
(select max(salary) from salaries)
)

第十九题

查找所有员工的last_name和first_name以及对应的dept_name，也包括暂时没有分配部门的员工

select last_name,first_name,dept_name FROM
employees as e left join dept_emp as de on e.emp_no=de.emp_no
left join departments as d on de.dept_no=d.dept_no
#三表联结

第二十一题（二十题没了）

查找在职员工自入职以来的薪水涨幅情况，给出在职员工编号emp_no以及其对应的薪水涨幅growth，并按照growth进行升序

select s1.emp_no,s1.salary-s2.salary as growth
from (select * from salaries where to_date='9999-01-01') as s1
left join (select * from salaries group by emp_no order by from_date)
as s2 on s1.emp_no=s2.emp_no
order by growth
/*先创建一个在职员工表s1，再创建一个入职员工表s2,
表s2为原始表中按工号分组，按工资初始时间排序，剪切
分组中，如果一组有多行，默认显示第一行*/

第二十二题

统计各个部门的工资记录数，给出部门编码dept_no、部门名称dept_name以及部门在salaries表里面有多少条记录sum，按照dept_no升序排序

select d.dept_no,d.dept_name,count(*)
as sum from departments as d join dept_emp on d.dept_no=dept_emp.dept_no
join salaries as s on dept_emp.emp_no=s.emp_no
group by d.dept_no order by d.dept_no ASC
#注意各表的联结键

第二十三题

对所有员工的薪水按照salary降序进行1-N的排名，要求相同salary并列且按照emp_no升序排列

select emp_no,salary,
dense_rank() over (order by salary DESC)
as t_rank from salaries
#使用窗口函数，相同并列排名dense_rank()

第二十四题

获取所有非manager员工薪水情况，给出dept_no、emp_no以及salary

select de.dept_no,s.emp_no,s.salary
from employees as e,salaries as s,dept_emp as de,dept_manager
where e.emp_no=de.emp_no
and de.emp_no=s.emp_no
and s.emp_no=dept_manager.emp_no
and de.dept_no not in (select emp_no from dept_manager)
#做出来的答案是相反的，取出了manager里员工的数据。待查
SELECT de.dept_no, s.emp_no, s.salary 
FROM dept_emp AS de INNER JOIN salaries AS s ON
s.emp_no = de.emp_no AND s.to_date = '9999-01-01'
WHERE de.emp_no NOT IN (SELECT emp_no FROM dept_manager
WHERE to_date = '9999-01-01')
#答案

第二十五题

获取员工其当前的薪水比其manager当前薪水还高的相关信息，
第一列给出员工的emp_no，
第二列给出其manager的manager_no，
第三列给出该员工当前的薪水emp_salary,
第四列给该员工对应的manager当前的薪水manager_salary

select de.emp_no,dema.emp_no as manager_no,
s1.salary as emp_salary,s2.salary as manager_salary
from dept_emp as de,dept_manager as dema,salaries as s1,salaries as s2
where de.dept_no=dema.dept_no
and de.emp_no=s1.emp_no
and dema.emp_no=s2.emp_no
and s1.salary>s2.salary

第二十六题

汇总各个部门当前员工的title类型的分配数目，即结果给出部门编号dept_no、dept_name、其部门下所有的员工的title以及该类型title对应的数目count，结果按照dept_no升序排序

select depart.dept_no,depart.dept_name,t.title,
count(t.title) as count
from departments as depart join dept_emp
on depart.dept_no=dept_emp.dept_no
join titles as t on dept_emp.emp_no=t.emp_no
group by depart.dept_no,t.title
order by depart.dept_no ASC

第二十八题（27没了）

查找描述信息(film.description)中包含robot的电影对应的分类名称(category.name)以及电影数目(count(film.film_id))，而且还需要该分类包含电影总数量(count(film_category.category_id))>=5部

select category.name,count(film.film_id)
from film join film_category on film.film_id=film_category.film_id
join category on film_category.category_id=category.category_id
where film.description like '%robot%'
and category.category_id IN
(select category_id from film_category
group by category_id having count(film_id)>=5)
/*注意这里要求该分类包含电影总数量>=5，
需先查找电影总数>=5的分类*/

第二十九题

使用join查询方式找出没有分类的电影id以及名称

select film_id,title from film
where film_id not in(
select f.film_id from
film as f join film_category as fc on f.film_id=fc.film_id
left join category as c on fc.category_id=c.category_id)

第三十题

使用子查询的方式找出属于Action分类的所有电影对应的title,description

select title,description FROM film
where film_id in(
select f.film_id from film f join film_category fc
on f.film_id=fc.film_id
join category c on fc.category_id=c.category_id
where c.name like 'Action')

第三十二题（31没了）

将employees表的所有员工的last_name和first_name拼接起来作为Name，中间以一个空格区分

select concat(last_name," ",first_name) as name from employees
#拼接字段，concat

第三十三题

创建一个actor表，包含如下列信息

create table actor(
actor_id smallint(5) primary key not null,
first_name varchar(45) not null,
last_name varchar(45) not null,
last_update date not null)
#创建表，字段 类型 约束，

第三十四题

请你对于表actor批量插入如下数据(不能有2条insert语句哦!)

insert into actor values
(1,'PENELOPE','GUINESS','2006-02-15 12:34:33'),
(2,'NICK','WAHLBERG','2006-02-15 12:34:33')
/*整行插入，insert into 表 values
注意非数字的字段要用引号（包括日期字段）
插入多行时，每行数据加括号，各行用逗号隔开*/

第三十五题

对于表actor插入如下数据,如果数据已经存在，请忽略(不支持使用replace操作)
即，数据已有则忽略本条插入，若无则创建本条插入

insert ignore into actor values
(3,'ED','CHASE','2006-02-15 12:34:33')
#insert ignote into 

第三十六题

创建一个actor_name表，并且将actor表中的所有first_name以及last_name导入该表.

create table  if not exists actor_name(
first_name varchar(45) not null,
last_name varchar(45) not null)
select first_name,last_name from actor
#先创建一个表，再用select从另一个表里导出

第三十七题

对first_name创建唯一索引uniq_idx_firstname，对last_name创建普通索引idx_lastname

alter table actor add unique 
uniq_idx_firstname(first_name);
alter table actor add index 
idx_lastname(last_name)
/*创建索引的方法：
索引值必须是唯一的，且不能为NULL：
ALTER TABLE tbl_name ADD UNIQUE index_name (col_list)
添加主键：
ALTER TABLE tbl_name ADD PRIMARY KEY (col_list)
普通索引，索引值可出现多次：
ALTER TABLE tbl_name ADD INDEX index_name (col_list)
指定了索引为 FULLTEXT ，用于全文索引：
ALTER TABLE tbl_name ADD FULLTEXT index_name (col_list)
删除索引：
DROP INDEX index_name ON tbl_name
ALTER TABLE tbl_name DROP INDEX index_name
ALTER TABLE tbl_name DROP PRIMARY KEY
*/

第三十八题

针对actor表创建视图actor_name_view，只包含first_name以及last_name两列，并对这两列重新命名，first_name为first_name_v，last_name修改为last_name_v

create view actor_name_view as select
first_name as first_name_v,last_name as last_name_v
from actor
/*创建视图：
1、create view view_name(view_list1,view_list2) as
select col_list1,col_list2 from table_name
2、create view view_name as select
col_list1 as view_list1,col_list2 as view_list2
from table_name
*/

第三十九题

针对salaries表emp_no字段创建索引idx_emp_no，查询emp_no为10005，使用强制索引

select * from salaries force index(idx_emp_no)
where emp_no=10005
#强制索引force index()

第四十题

在last_update后面新增加一列名字为create_date, 类型为datetime, NOT NULL，默认值为’2020-10-01 00:00:00’

alter table actor add column create_date
datetime NOT NULL default '2020-10-01 00:00:00'
#插入列，alter table table_name add column col_name
#设默认值，放最后 default '默认值'

第四十一题

构造一个触发器audit_log，在向employees_test表中插入一条数据的时候，触发插入相关的数据到audit中

create trigger audit_log
after insert on employees_test 
for each ROW
begin
insert into audit values(new.id,new.name);
end
/*构造触发器，
CREATE TRIGGER trigger_name
trigger_time trigger_event ON tbl_name
FOR EACH ROW
trigger_stmt
其中trigger_stmt是触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句，每条语句结束要分号结尾
在insert触发器中，new表示插入的新数据
在update触发器中，old表示被修改的原数据，new表示新数据
在delete触发器中，old表示被删除的原数据
old只读，new可用set赋值
*/

第四十二题

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

delete from titles_test
WHERE id not in(
select * from (
select min(id) from titles_test
group by emp_no) as new_t)
#在查询的时候不能把表删了，所以再创建一个查询的表，并重命名

第四十三题

将所有to_date为9999-01-01的全部更新为NULL,且 from_date更新为2001-01-01

update titles_test set
to_date=NULL,from_date='2001-01-01'
where to_date='9999-01-01'
#更新：update 表名 set 列名=新值 where 列名=旧值 

第四十四题

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

update titles_test SET
emp_no=replace(emp_no,10001,10005)
where id=5
/*使用replace:
update 表名 set 列名=replace(string,from_str,to_str)
把from_str替换成to_str*/

第四十五题

将titles_test表名修改为titles_2017

alter table titles_test rename to titles_2017
#alter table 表名 rename TO/AS 新表名 

第四十六题

在audit表上创建外键约束，其emp_no对应employees_test表的主键id

alter table audit
add constraint foreign key(emp_no)
references employees_test(id)
/*创建外键：
alter table 表名
add constraint foreign key(列名)
references 关联表(关联列)
*/

第四十八题（47没了）

写出更新语句，将所有获取奖金的员工当前的(salaries.to_date=‘9999-01-01’)薪水增加10%。(emp_bonus里面的emp_no都是当前获奖的所有员工)

update salaries as s right join emp_bonus as e
on s.emp_no=e.emp_no
set s.salary=s.salary*1.1
where s.to_date='9999-01-01'

第五十题（49没了）

将employees表中的所有员工的last_name和first_name通过(’)连接起来。(sqlite不支持concat，请用||实现，mysql支持concat)

select concat (last_name,"'",first_name)
from employees
#用concat连接的方法,mysql
select last_name ||"'"|| first_name from employees
#splite不支持concat，词方法MySQL不适用

第五十一题

查找字符串’10,A,B’ 中逗号’,'出现的次数cnt

select length('10,A,B')-length(replace('10,A,B',',',''))
#原字符长度-把要统计的内容去掉后的字符长度
#count计算的是表中行的数目，不可用于字符串

第五十二题

获取Employees中的first_name，查询按照first_name最后两个字母，按照升序进行排列

select first_name from employees
order by right(first_name,2) ASC
#最后两个字母，right(列名,位数)

第五十三题

按照dept_no进行汇总，属于同一个部门的emp_no按照逗号进行连接，结果给出dept_no以及连接出的结果employees

select dept_no,
group_concat(emp_no) as employees
from dept_emp group by dept_no
/*连接字段：group_concat( [distinct] 要连接的字段 
[order by 排序字段 asc/desc ] [separator '分隔符']
distinct用来排除重复值
separator是一个字符串值，缺省值为逗号
*/

第五十四题

查找排除最大、最小salary之后的当前(to_date = ‘9999-01-01’ )员工的平均工资avg_salary

方法一：子查询
select avg(salary) as avg_salary from salaries as s
where s.to_date = '9999-01-01'
and salary not in (select max(s1.salary) from salaries as s1
where s1.to_date = '9999-01-01')
and salary not in (select min(s2.salary) from salaries as s2
where s2.to_date = '9999-01-01')
方法二：去掉最大最小值计算
SELECT (SUM(salary)-MAX(salary)-MIN(salary))/(COUNT(emp_no)-2)
as avg_salary FROM salaries
where to_date = '9999-01-01'

第五十五题

分页查询employees表，每5行一页，返回第2页的数据

select * from employees limit 5,5
/*分页查询，limit:limit X,Y
X:返回几条记录
Y:从第几条记录开始返回（第一条记录序号为0，默认为0）*/

第五十七题（56没了）

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

不用exists：
select * from employees
where emp_no not in(
select emp_no from dept_emp
where employees.emp_no=dept_emp.emp_no)
用exists:
select * from employees
where not exists(
select emp_no from dept_emp
where employees.emp_no=dept_emp.emp_no)
/*EXISTS语句：执行employees.length次
指定一个子查询，检测行的存在。
遍历循环外表，然后看外表中的记录有没有和内表的数据一样的。
匹配上就将结果放入结果集中*/

第五十九题（58没了）

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

select e.emp_no,e.first_name,e.last_name,b.btype,s.salary,
case when b.btype=1 then s.salary*0.1
when b.btype=2 then s.salary*0.2
else s.salary*0.3 end bonus
from employees as e,emp_bonus as b,salaries as s 
where b.emp_no=e.emp_no
and s.emp_no=e.emp_no
and s.to_date='9999-01-01'
/*case语句：
--简单Case函数
CASE sex
WHEN '1' THEN '男'
WHEN '2' THEN '女'
ELSE '其他' END
--Case搜索函数
CASE WHEN sex = '1' THEN '男'
WHEN sex = '2' THEN '女'
ELSE '其他' END
*/

第六十题

统计salary的累计和running_total，running_total为前N个当前( to_date = ‘9999-01-01’)员工的salary累计和

窗口函数：
select emp_no,salary,
sum(salary) over(order by emp_no ASC) as running_total
from salaries
where to_date = '9999-01-01'
#窗口函数sum(求和项) over(order by 排序项)
嵌套查询：
select s.emp_no,s.salary,
(select sum(s1.salary) from salaries s1
where s1.emp_no <= s.emp_no
and s1.to_date = '9999-01-01'
) as running_total from salaries s
where s.to_date = '9999-01-01'
order by s.emp_no ASC

第六十一题

输出first_name排名(按first_name升序排序)为奇数的first_name

select e.first_name from employees e join
(select first_name,row_number() over 
(order by first_name ASC) as r
from employees) e2
on e.first_name=e2.first_name
where e2.r %2 != 0
#注意题目要求不改变原表顺序

第六十二题

写一个sql查询，积分表里面出现三次以及三次以上的积分

select distinct number from grade
group by number
having count(*)>=3

第六十三题

输出通过的题目的排名，通过题目个数相同的，排名相同，此时按照id升序排列

select id,number,
dense_rank() over (order by number DESC) t_rank 
from passing_number
order by number DESC,id ASC
/*排序的多种形式
rou_number:不存在并列
不管有无重复，均为1、2、3、。。。
rank():存在并列，排名间断
相同则排名相同，如1、1、3、3、5、6
dense_rank():存在并列，排名连续
如1、1、2、2、3、4
*/

第六十四题

找到每个人的任务情况，并且输出出来，没有任务的也要输出，而且输出结果按照person的id升序排序

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

第六十五题

写一个sql查询，每一个日期里面，正常用户发送给正常用户邮件失败的概率是多少，结果保留到小数点后面3位(3位之后的四舍五入)，并且按照日期升序排序

SELECT date, 
ROUND(SUM(CASE type WHEN 'completed' THEN 0 ELSE 1
END)*1.0 #由于需要把数字转化为浮点型，这里要*1.0
/ COUNT(type),3)
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

第六十六题

查询每个用户最近一天登录的日子，并且按照user_id升序排序

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

第六十七题

查询每个用户最近一天登录的日子，用户的名字，以及用户用的设备的名字，并且查询结果按照user的name升序排序

select user.name as u_n,client.name as c_n,login.date as date
from user left join login on user.id=login.user_id
join client on login.client_id=client.id
where (login.user_id,login.date) in
(select user_id,max(date) from login group by user_id)
order by user.name ASC

第六十八题

查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)

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)
/*日期相关函数：
DATE_SUB(date,INTERVAL expr type)：减去时间间隔
DATE_ADD(date,INTERVAL expr type)：加上时间间隔
DATE_FORMAT(date,format)：改变输出格式
str_to_date(str_date,’%Y-%m-%d’)：将字符串转成日期格式*/

第六十九题

查询每个日期登录新用户个数，并且查询结果按照日期升序排序

select distinct date,
sum(case 
when (user_id,date) in
(select user_id,min(date) from login
group by user_id)
then 1 else 0 end)
from login 
group by date
order by date ASC

第七十题

查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序

select date,ifnull(round(
sum(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 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
/*次留=第二天还存在的新用户总数/第一天的所有新用户
如果用(user_id,date) in
(select user_id,date_add(min(date),INTERVAL 1 DAY))
分子分母的日期不同步，遍历后得到的结果是0*/

第七十一题

查询刷题信息，包括: 用户的名字，以及截止到某天，累计总共通过了多少题，并且查询结果先按照日期升序排序，再按照姓名升序排序，有登录却没有刷题的哪一天的数据不需要输出

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

第七十二题

查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面3位

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

第七十三题

查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性，并且以id的升序排序

select g.id,g.job,g.score
from grade g join 
(select job,avg(score) avg_score
from grade group by job) g2
on g.job=g2.job
where g.score>g2.avg_score
order by g.id ASC

第七十四题

找出每个岗位分数排名前2名的用户，得到的结果先按照language的name升序排序，再按照积分降序排序，最后按照grade的id升序排序

select g.id,l.name,g.score
from language as l join
(select id,language_id,score,
dense_rank() over(partition by language_id order by score DESC) as s_rank
from grade) as g ON
l.id=g.language_id
where s_rank<=2
order by l.name,g.score DESC,g.id
#命名的时候注意不要和公式重名，s_rank之前命名为rank报错

第七十五题

查询各个岗位分数升序排列之后的中位数位置的范围，并且按job升序排序

select job,
case when count(score)%2=0 then round(count(score)/2,0)
else ceiling(count(score)/2) end as start,
case when count(score)%2=0 then round(count(score)/2,0)+1
else ceiling(count(score)/2) end as end
from grade group by job order by job
/*round(x,n):四舍五入把原值转化为n位小数
floor()向下舍入为指定小数位数
ceiling()向上舍入为指定小数位数
*/

第七十六题

查询各个岗位分数的中位数位置上的所有grade信息，并且按id升序排序

select g1.id,g1.job,g1.score,g1.t_rank from
(select id,job,score,
row_number()over(partition by job order by score DESC)
as t_rank from grade) as g1 JOIN
(select job,
case when count(score)%2=0 then round(count(score)/2,0)
else ceiling(count(score)/2) end as start,
case when count(score)%2=0 then round(count(score)/2,0)+1
else ceiling(count(score)/2) end as end
from grade group by job order by job
) as m
on g1.job=m.job
where g1.t_rank>=m.start
and g1.t_rank<=m.end
order by id

第七十七题

查询在2025-10-15以后状态为购买成功的C++课程或者Java课程或者Python的订单，并且按照order_info的id升序排序

select * from order_info
where date>'2025-10-15'
and status='completed'
and product_name in ('C++','Java','Python')
order by id ASC

第七十八题

查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的user_id，并且按照user_id升序排序

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

第七十九题

查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的订单信息，并且按照order_info的id升序排序

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

第八十题

查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序

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

第八十一题

查询在2025-10-15以后，如果有一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程，那么输出这个用户的user_id，以及满足前面条件的第一次购买成功的C++课程或Java课程或Python课程的日期first_buy_date，以及满足前面条件的第二次购买成功的C++课程或Java课程或Python课程的日期second_buy_date，以及购买成功的C++课程或Java课程或Python课程的次数cnt，并且输出结果按照user_id升序排序

select user_id,
min(case when t_rank=1 then date end) as first_buy_date,
min(case when t_rank=2 then date end) as second_buy_date,
/*这里的min是因为同一天同一个人可能有多条记录，
多条记录无法显示为一条，换成max也一样*/
cnt from
(
select *,count(id) over(partition by user_id) as cnt,
#每个用户的订单量
row_number() over(partition by user_id order by date) as t_rank
#按日期升序排列
from order_info
where status="completed"
and product_name in ("C++","Java","Python")
and date>'2025-10-15'
#找到除了订单数>2以外，符合其它条件的结果。按时间排序
) as o1
where cnt>1
group by user_id

第八十二题

查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的订单id，是否拼团以及客户端名字信息，最后一列如果是非拼团订单，则显示对应客户端名字，如果是拼团订单，则显示NULL，并且按照order_info的id升序排序

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

第八十三题

查询在2025-10-15以后，同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的来源信息，第一列是显示的是客户端名字，如果是拼团订单则显示GroupBuy，第二列显示这个客户端(或者是拼团订单)有多少订单，最后结果按照第一列(source)升序排序

SELECT
case when o.is_group_buy='No' then c.name 
else 'GroupBuy' end source,
count(*) as cnt from 
order_info as o left join client as c 
on o.client_id = c.id
where o.user_id in 
(select user_id from order_info 
where date>'2025-10-15'
and product_name in ('C++','Java','Python')
and status='completed'
group by user_id
)
and date>'2025-10-15'
and product_name in ('C++','Java','Python')
group by c.id
order by source ASC

这个在牛客上结果多出来一个H5的数据，没找出来为什么

第八十四题

查询在2025年内投递简历的岗位和数量，并且按数量降序排序

select job,sum(num) as cnt
from resume_info
where year(date)='2025'
group by job
order by cnt DESC

第八十五题

查询在2025年内投递简历的每个岗位，每一个月内收到简历的数量，并且按先按月份降序排序，再按简历数目降序排序

select job,date_format(date,'%Y-%m') as mon,
sum(num) as cnt from resume_info
where date<'2026-01-01'
and date>'2024-12-31'
group by job,mon
order by mon DESC,cnt DESC
/*DATE_FORMAT(date,format)函数：
%a	缩写星期名
%b	缩写月名
%c	月，数值
%D	带有英文前缀的月中的天
%d	月的天，数值(00-31)
%e	月的天，数值(0-31)
%f	微秒
%H	小时 (00-23)
%h	小时 (01-12)
%I	小时 (01-12)
%i	分钟，数值(00-59)
%j	年的天 (001-366)
%k	小时 (0-23)
%l	小时 (1-12)
%M	月名
%m	月，数值(00-12)
%p	AM 或 PM
%r	时间，12-小时（hh:mm:ss AM 或 PM）
%S	秒(00-59)
%s	秒(00-59)
%T	时间, 24-小时 (hh:mm:ss)
%U	周 (00-53) 星期日是一周的第一天
%u	周 (00-53) 星期一是一周的第一天
%V	周 (01-53) 星期日是一周的第一天，与 %X 使用
%v	周 (01-53) 星期一是一周的第一天，与 %x 使用
%W	星期名
%w	周的天 （0=星期日, 6=星期六）
%X	年，其中的星期日是周的第一天，4 位，与 %V 使用
%x	年，其中的星期一是周的第一天，4 位，与 %v 使用
%Y	年，4 位
%y	年，2 位
*/

第八十六题

查询在2025年投递简历的每个岗位，每一个月内收到简历的数目，和对应的2026年的同一个月同岗位，收到简历的数目，最后的结果先按first_year_mon月份降序，再按job降序排序显示

select r1.job,first_year_mon,first_year_cnt,
second_year_mon,second_year_cnt from 
(select job,date_format(date,'%Y-%m') as first_year_mon,
sum(num) as first_year_cnt from resume_info
where date>'2024-12-31'
and date<'2026-01-01'
group by first_year_mon,job) as r1 left join 
(select job,date_format(date,'%Y-%m') as second_year_mon,
sum(num) as second_year_cnt from resume_info
where date>'2025-12-31'
and date<'2027-01-01'
group by second_year_mon,job) as r2 on 
r1.job=r2.job
and right(r1.first_year_mon,2)=right(r2.second_year_mon,2)
order by first_year_mon DESC,job DESC

第八十七题

如果一个学生知道了自己综合成绩以后，最差是排第几名? 结果按照grade升序排序

select grade,
sum(number) over(order by grade ASC) as t_rank
from class_grade order by grade ASC

第八十八题

查询中位数，如果只有1个中位数，输出1个，如果有2个中位数，按grade升序输出

select grade from 
(select grade,
(select sum(number) from class_grade) as num,
sum(number) over(order by grade ASC) as a,
sum(number) over(order by grade DESC) as b
from class_grade) as c
where a>=num/2
and b>=num/2
order by grade

第八十九题

查找积分增加最高的用户的名字，以及他的总积分是多少(此题数据保证积分最高的用户有且只有1个)

select u.name,
sum(g.grade_num) over(partition by g.user_id) as grade
from user u join grade_info g 
on u.id=g.user_id
order by grade DESC
limit 1

第九十题

查找积分增加最高的用户的id(可能有多个)，名字，以及他的总积分是多少，查询结果按照id升序排序

with t0 as (
select u.id,u.name,sum(g.grade_num) as grade_sum
from user as u join grade_info as g 
on u.id=g.user_id
group by u.id
)
select id,name,grade_sum from t0
where grade_sum=(
select max(grade_sum) from t0)
order by id
#子查询创建表，with...as(表)

第九十一题

查找积分最高的用户的id，名字，以及他的总积分是多少(可能有多个)，查询结果按照id升序排序

with t0 as (
select u.id,u.name,
sum((case when g.type='add' then grade_num 
else -1*grade_num end)) as grade_sum 
from user as u join grade_info as g 
on u.id=g.user_id
group by u.id)
select * from t0 
where grade_sum=(select max(grade_sum) from t0)

第九十二题

查找购买个数超过20,质量小于50的商品，按照商品id升序排序

select g.id,g.name,g.weight,
sum(t.count) as total
from goods as g join trans as t 
on g.id=t.goods_id
group by g.id
having(total>20 and g.weight<50)
order by g.id ASC
#有group by的时候，条件用having不用where

第九十三题

查询向user_id = 1 的用户，推荐其关注的人喜欢的音乐。
不要推荐该用户已经喜欢的音乐，并且按music的id升序排列。你返回的结果中不应当包含重复项

select distinct(m.music_name) as music_name from 
follow f left join music_likes l on 
f.follower_id=l.user_id 
join music m on 
l.music_id=m.id
where f.user_id=1
and l.music_id not in (
select music_id from music_likes 
where user_id=1)
order by m.id ASC