【Ryo】MySQL：刷题训练牛客篇（三）

数据库是进行数据分析的“源泉”，对冗杂的数据进行整理和编排是数据分析的首要任务。同时将来职业生活中，对数据库系统进行DB操作也会是重要任务之一，因此掌握数据库语言刷题刷起来，至少对语言运用和数据库管理有所了解是题中之义。
本篇为牛客题霸的SQL训练题目练习记录（三）。

研究内容	语言	日期
数据库	sql	2021年1月29日

△△△△△本文为个人项目练习，仅供参考，如有不足欢迎讨论 △△△△△

1. 连接表

题目描述：

个人解答：

#把串 "10,A,B" 中的逗号用空串替代， 变成了 "10AB"，然后原来串的长度 - 替换之后的串的长度，就是被替换的逗号的个数。
select p.id, name, content
from person as p
left join task as t
on t.person_id = p.id
order by p.id

注：左连接注意主表，因为Null 也要输出

2. 分组查询

题目描述：

个人解答：

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

3. 多表连接子查询

题目描述：


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

牛客解答：

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

注：本题连接+子查询有难度

4. 聚合函数

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

`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');

个人解答：

select avg(a.salary) as avg_salary
from salaries as a
where a.to_date='9999-01-01'
and a.salary not in (select max(b.salary) from salaries b where b.to_date='9999-01-01')
and a.salary not in (select min(b.salary) from salaries b where b.to_date='9999-01-01');

牛客给出第二种题解：（使用聚合函数，不用子查询。COUNT(1) 代表所有数据长度， -2 代表减去最大最小值的两个长度 ）

SELECT (SUM(salary) - MAX(salary) - MIN(salary)) / (COUNT(1)-2) avg_salary
FROM salaries where to_date = '9999-01-01';

5. 分页查询

题目描述：
分页查询employees表，每5行一页，返回第2页的数据

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`));

个人解答：

select * from employees
order by emp_no
limit 5,5;

6.左连接

题目描述：
获取所有员工的emp_no、部门编号dept_no以及对应的bonus类型btype和received，没有分配奖金的员工不显示对应的bonus类型btype和received。

 CREATE TABLE `dept_emp` (
`emp_no` int(11) NOT NULL,
`dept_no` char(4) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`dept_no`));

CREATE TABLE `emp_bonus`(
emp_no int(11) NOT NULL,
received datetime NOT NULL,
btype smallint(5) NOT NULL);

CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` char(1) NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)); 

个人解答：

select de.emp_no, de.dept_no, eb.btype, eb.received
from dept_emp as de
left join emp_bonus as eb
on de.emp_no=eb.emp_no