数据库是进行数据分析的“源泉”,对冗杂的数据进行整理和编排是数据分析的首要任务。同时将来职业生活中,对数据库系统进行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