第一题
链接: 牛客248
描述
查找排除在职(to_date = '9999-01-01' )员工的最大、最小salary之后,其他的在职员工的平均工资avg_salary。
CREATE TABLE `salaries` ( `emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
如:
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,70698,'2000-11-27','2001-11-27');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
输出格式:
思路:通过子查询将最大最小年龄的排除
示例
drop table if exists `salaries` ;
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` float(11,3) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
INSERT INTO salaries VALUES(10001,85097,'2001-06-22','2002-06-22');
INSERT INTO salaries VALUES(10001,88958,'2002-06-22','9999-01-01');
INSERT INTO salaries VALUES(10002,72527,'2001-08-02','9999-01-01');
INSERT INTO salaries VALUES(10003,43699,'2000-12-01','2001-12-01');
INSERT INTO salaries VALUES(10003,43311,'2001-12-01','9999-01-01');
INSERT INTO salaries VALUES(10004,70698,'2000-11-27','2001-11-27');
INSERT INTO salaries VALUES(10004,74057,'2001-11-27','9999-01-01');
SQL
select
avg(salary) as avg_salary
from
salaries
where
salary not in (
select
max(salary)
from
salaries
where
to_date = '9999-01-01'
)
and salary not in (
select
min(salary)
from
salaries
where
to_date = '9999-01-01'
)
and to_date = '9999-01-01';
第二题
链接: 牛客257
描述
在牛客刷题有一个通过题目个数的(passing_number)表,id是主键,简化如下:
第1行表示id为1的用户通过了4个题目;
…
第6行表示id为6的用户通过了4个题目;
请你根据上表,输出通过的题目的排名,通过题目个数相同的,排名相同,此时按照id升序排列,数据如下:
id为5的用户通过了5个排名第1,
id为1和id为6的都通过了4个,并列第2
思路:使用窗口函数dense_rank()
1、rank()排序相同时会重复,总数不变,即会出现1、1、3这样的排序结果;
2、dense_rank()排序相同时会重复,总数会减少,即会出现1、1、2这样的排序结果;
3、row_number()排序相同时不会重复,会根据顺序排序。
示例
drop table if exists passing_number;
CREATE TABLE `passing_number` (
`id` int(4) NOT NULL,
`number` int(4) NOT NULL,
PRIMARY KEY (`id`));
INSERT INTO passing_number VALUES
(1,4),
(2,3),
(3,3),
(4,2),
(6,4),
(5,5);
SQL
select
id,
number,
dense_rank () over (
ORDER BY
number DESC
) ` rank `
from
passing_number
order by
number desc
第三题
链接: 牛客267
描述
牛客每次考试完,都会有一个成绩表(grade),如下:
第1行表示用户id为1的用户选择了C++岗位并且考了11001分
。。。
第8行表示用户id为8的用户选择了前端岗位并且考了9999分
请你写一个sql语句查询用户分数大于其所在工作(job)分数的平均分的所有grade的属性,并且以id的升序排序,如下:
思路:这题用子查询就能解决了
示例
drop table if exists grade;
CREATE TABLE grade(
`id` int(4) NOT NULL,
`job` varchar(32) NOT NULL,
`score` int(10) NOT NULL,
PRIMARY KEY (`id`));
INSERT INTO grade VALUES
(1,'C++',11001),
(2,'C++',10000),
(3,'C++',9000),
(4,'Java',12000),
(5,'Java',13000),
(6,'JS',12000),
(7,'JS',11000),
(8,'JS',9999),
(9,'Java',12500);
SQL
select
*
from
grade gr
where
score > (
select
avg(score)
from
grade g
where
gr.job = g.job
group by
g.job
)
第四题
链接: 牛客272
描述
有一个员工表employees简况如下:
第1行表示user_id为557336的用户在2025-10-10的时候使用了client_id为1的客户端下了C++课程的订单,但是状态为没有购买成功。
第2行表示user_id为230173543的用户在2025-10-12的时候使用了client_id为2的客户端下了Python课程的订单,状态为购买成功。
。。。
最后1行表示user_id为557336的用户在2025-10-25的时候使用了client_id为1的客户端下了C++课程的订单,状态为购买成功。
请你写出一个sql语句查询在2025-10-15以后,同一个用户下单2个以及2个以上状态为购买成功的C++课程或Java课程或Python课程的user_id,并且按照user_id升序排序,以上例子查询结果如下:
解析:
id为4,6的订单满足以上条件,输出对应的user_id为57;
id为5,7的订单满足以上条件,输出对应的user_id为557336;
按照user_id升序排序。
思路:先根据条件查询出相应数据在求和 ,关键字having
示例
drop table if exists order_info;
CREATE TABLE order_info (
id int(4) NOT NULL,
user_id int(11) NOT NULL,
product_name varchar(256) NOT NULL,
status varchar(32) NOT NULL,
client_id int(4) NOT NULL,
date date NOT NULL,
PRIMARY KEY (id));
INSERT INTO order_info VALUES
(1,557336,'C++','no_completed',1,'2025-10-10'),
(2,230173543,'Python','completed',2,'2025-10-12'),
(3,57,'JS','completed',3,'2025-10-23'),
(4,57,'C++','completed',3,'2025-10-23'),
(5,557336,'Java','completed',1,'2025-10-23'),
(6,57,'Java','completed',1,'2025-10-24'),
(7,557336,'C++','completed',1,'2025-10-25');
SQL
select
user_id
from
order_info
where
status = 'completed'
and product_name in ('C++', 'Java', 'Python')
and date > '2025-10-15'
group by
user_id
having
count(user_id) >= 2
order by
user_id