SQL刷题第四天 牛客

题一：每个人最近的登录日期

牛客每天有很多人登录，请你统计一下牛客每个用户最近登录是哪一天。

有一个登录(login)记录表，简况如下:

id	user_id	client_id	date
1 2 3 4	2 3 2 3	1 2 2 2	2020-10-12 2020-10-12 2020-10-13 2020-10-13

第1行表示user_id为2的用户在2020-10-12使用了客户端id为1的设备登录了牛客网
。。。
第4行表示user_id为3的用户在2020-10-13使用了客户端id为2的设备登录了牛客网

请你写出一个sql语句查询每个用户最近一天登录的日子，并且按照user_id升序排序，上面的例子查询结果如下:

user_id	id
2 3	2020-10-13 2020-10-13

查询结果表明:
user_id为2的最近的登录日期在2020-10-13
user_id为3的最近的登录日期也是2020-10-13

 方法（分组+升序排序）：

select user_id,Max(date) as id
from  login 
group by user_id 
order by user_id asc

题二：考试分数

牛客每次考试完，都会有一个成绩表(grade)，如下:

id	job	score
1	C++	11001
2	C++	10000
3	C++	9000
4	Java	12000
5	Java	13000
6	JS	12000
7	JS	11000
8	JS	9999

第1行表示用户id为1的用户选择了C++岗位并且考了11001分

。。。

第8行表示用户id为8的用户选择了JS岗位并且考了9999分

请你写一个sql语句查询各个岗位分数的平均数，并且按照分数降序排序，结果保留小数点后面3位(3位之后四舍五入):

job	avg
Java	12500.000
JS	10999.667
C++	10000.333

(注意: sqlite 1/2得到的不是0.5，得到的是0，只有1*1.0/2才会得到0.5，sqlite四舍五入的函数为round)

方法（Round取小数点后三位，avg取平均值）：

select job,ROUND(avg(score),3) as 'avg' from grade
group by job
order by avg desc

题三：查找入职员工时间排名倒数第三的员工所有信息

有一个员工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	Christian	Koblick	M	1986-12-01

请你查找employees里入职员工时间排名倒数第三的员工所有信息，以上例子输出如下:

emp_no	birth_date	first_name	last_name	gender	hire_date
10001	1953-09-02	Georgi	Facello	M	1986-06-26

注意：可能会存在同一个日期入职的员工，所以入职员工时间排名倒数第三的员工可能不止一个。

 方法（limit .. offset .. 取第几个，distinct 去重 可能存在倒个排名一样的）：

SELECT * 
FROM employees
WHERE hire_date = (
    SELECT DISTINCT hire_date 
    FROM employees
    ORDER BY hire_date DESC       -- 倒序
    LIMIT 1 OFFSET 2              -- 去掉排名倒数第一第二的时间，取倒数第三
);    

题四： 查找所有已经分配部门的员工的last_name和first_name以及dept_no

有一个员工表，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	Christian	Koblick	M	1986-12-01

有一个部门表，dept_emp简况如下:

emp_no	dept_no	from_date	to_date
10001	d001	1986-06-26	9999-01-01
10002	d002	1989-08-03	9999-01-01

请你查找所有已经分配部门的员工的last_name和first_name以及dept_no，未分配的部门的员工不显示，以上例子如下:

last_name	first_name	dept_no
Facello	Georgi	d001
Simmel	Bezalel	d002

方法（左连接+拿出部门号不为空的员工） ：

select e.last_name ,e.first_name ,d.dept_no
    from  employees e left join dept_emp d
    on e.emp_no = d.emp_no 
    where d.dept_no is not null

题五：查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t

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

emp_no	salary	from_date	to_date
10001	60117	1986-06-26	1987-06-26
10001	62102	1987-06-26	1988-06-25
10001	66074	1988-06-25	1989-06-25
10001	66596	1989-06-25	1990-06-25
10001	66961	1990-06-25	1991-06-25
10001	71046	1991-06-25	1992-06-24
10001	74333	1992-06-24	1993-06-24
10001	75286	1993-06-24	1994-06-24
10001	75994	1994-06-24	1995-06-24
10001	76884	1995-06-24	1996-06-23
10001	80013	1996-06-23	1997-06-23
10001	81025	1997-06-23	1998-06-23
10001	81097	1998-06-23	1999-06-23
10001	84917	1999-06-23	2000-06-22
10001	85112	2000-06-22	2001-06-22
10001	85097	2001-06-22	2002-06-22
10002	72527	1996-08-03	1997-08-03

请你查找薪水记录超过15条的员工号emp_no以及其对应的记录次数t，以上例子输出如下:

emp_no	t
10001	16

方法（分组用法考察group by having） ：

select emp_no,count(emp_no) from salaries
group by emp_no
having count(emp_no) > 15