SQL练习（Day02）

最新推荐文章于 2024-08-30 13:51:08 发布

谁人骨

最新推荐文章于 2024-08-30 13:51:08 发布

阅读量80

点赞数

文章标签： sql 数据库 mysql

本文链接：https://blog.csdn.net/weixin_56926306/article/details/127157752

版权

第一题

链接: 牛客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