SQL刷题第二天 牛客

题一：查找employees表emp_no与last_name的员工信息

有一个员工表employees简况如下:

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezalel	Simmel	F	1985-11-21
10003	1959-12-03	Bezalel	Mary	M	1986-08-28
10004	1954-05-01	Christian	Koblick	M	1986-12-01
10005	1953-11-07	Mary	Sluis	F	1990-01-22

请你查找employees表所有emp_no为奇数，且last_name不为Mary的员工信息，并按照hire_date逆序排列，以上例子查询结果如下:

emp_no	birth_date	first_name	last_name	gender	hire_date
10005	1953-11-07	Mary	Sluis	F	1990-01-22
10001	1953-09-02	Georgi	Facello	M	1986-06-26

提示： emp_no为奇数用取余数方法解决(MOD(被除数, 除数))或者emp_no % 2

方法：44ms

select * from employees
    where MOD(emp_no,2) != 0 and last_name != 'Mary' #emp_no为奇数，且last_name不为Mary的员工信息
    order by hire_date desc #逆序排列

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

有一个薪水表salaries简况如下:

emp_no	salary	from_date	to_date
10001	88958	2002-06-22	9999-01-01
10002	72527	2001-08-02	9999-01-01
10003	43311	2001-12-01	9999-01-01

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

若有多个员工的薪水为第二多的薪水，则将对应的员工的emp_no和salary全部输出，并按emp_no升序排序。

emp_no	salary
10002	72527

提示：题目没有要求去去重(distinct) 、 去掉(offset)；考虑到工资第一多第二多的员工都有可能有多个，所以需要将其按照工资分组再排序

select emp_no , salary from salaries
    where salary = (
        select salary from salaries
         group by salary
         order by salary desc
        limit 1 offset 1
    )

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

现有员工表employees如下：

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26
10002	1964-06-02	Bezalel	Simmel	F	1985-11-21
10003	1959-12-03	Parto	Bamford	M	1986-08-28
10004	1954-05-01	Chirstian	Koblick	M	1986-12-01
10005	1955-01-21	Kyoichi	Maliniak	M	1989-09-12
10006	1953-04-20	Anneke	Preusig	F	1989-06-02
10007	1957-05-23	Tzvetan	Zielinski	F	1989-02-10
10008	1958-02-19	Saniya	Kalloufi	M	1994-09-15
10009	1952-04-19	Sumant	Peac	F	1985-02-18
10010	1963-06-01	Duangkaew	Piveteau	F	1989-08-24
10011	1953-11-07	Mary	Sluis	F	1990-01-22

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

输出描述：

Name
Facello Georgi
Simmel Bezalel
Bamford Parto
Koblick Chirstian
Maliniak Kyoichi
Preusig Anneke
Zielinski Tzvetan
Kalloufi Saniya
Peac Sumant
Piveteau Duangkaew
Sluis Mary

(注：sqllite,字符串拼接为 || 符号，不支持concat函数，mysql支持concat函数)。

方法（主要考察concat方法） ：

select concat(last_name,' ',first_name) AS Name from employees

题四：批量插入数据

对于表actor批量插入如下数据
CREATE TABLE IF NOT EXISTS actor (
actor_id smallint(5) NOT NULL PRIMARY KEY,
first_name varchar(45) NOT NULL,
last_name varchar(45) NOT NULL,
last_update timestamp NOT NULL DEFAULT (datetime('now','localtime')))

actor_id	first_name	last_name	last_update
1	PENELOPE	GUINESS	2006-02-15 12:34:33
2	NICK	WAHLBERG	2006-02-15 12:34:33

insert into
    actor
values
    (1, 'PENELOPE', 'GUINESS', '2006-02-15 12:34:33'),
    (2, 'NICK', 'WAHLBERG', '2006-02-15 12:34:33');

个人建议：如果开发在可以使用EasyExcel接口进行数据的导入导出（后续会发布关于EasyExcel接口的使用教程）

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

删除emp_no重复的记录，只保留最小的id对应的记录。
CREATE TABLE IF NOT EXISTS titles_test (
id int(11) not null primary key,
emp_no int(11) NOT NULL,
title varchar(50) NOT NULL,
from_date date NOT NULL,
to_date date DEFAULT NULL);

insert into titles_test values ('1', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('2', '10002', 'Staff', '1996-08-03', '9999-01-01'),
('3', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('4', '10004', 'Senior Engineer', '1995-12-03', '9999-01-01'),
('5', '10001', 'Senior Engineer', '1986-06-26', '9999-01-01'),
('6', '10002', 'Staff', '1996-08-03', '9999-01-01'),

('7', '10003', 'Senior Engineer', '1995-12-03', '9999-01-01');

删除后titles_test表为(注：最后会select * from titles_test表来对比结果)

id	emp_no	title	from_date	to_date
1	10001	Senior Engineer	1986-06-26	9999-01-01
2	10002	Staff	1996-08-03	9999-01-01
3	10003	Senior Engineer	1995-12-03	9999-01-01
4	10004	Senior Engineer	1995-12-03	9999-01-01

 提示：不能一边查询该表一边进行操作，需要将该表转出中间表然后再进行查询删除

DELETE FROM titles_test
WHERE id NOT IN(
    SELECT * FROM(
    SELECT MIN(id)
    FROM titles_test
    GROUP BY emp_no) a
    );  -- 把得出的表重命名那就不是原表了