第一题

来源：牛客网，sql实战，第12题

获取所有部门中当前(dept_emp.to_date = ‘9999-01-01’)员工当前(salaries.to_date=‘9999-01-01’)薪水最高的相关信息，给出dept_no, emp_no以及其对应的salary，按照部门升序排列。

题目提供的建表语句：

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

错误示范

SELECT d.dept_no, d.emp_no, s.salary
FROM dept_emp as d
INNER JOIN salaries as s
ON d.emp_no=s.emp_no
WHERE d.to_date='9999-01-01’
AND s.to_date='9999-01-01'
GROUP BY d.dept_no
HAVING salary=MAX(s.salary);

错误点

• d.emp_no是非聚合字段，不能出现在SELECT。因为一个聚合字段(dept_no)对应多个非聚合字段(emp_no)，所以选择的时候，会随机选择非聚合字段中的任何一个，于是出错。
• 根据上一个错误点，如果强行写上述代码，当多人同时拥有最高薪水时也无法查出。
• HAVING语句用来与聚合函数联合使用，过滤GROUP BY语句返回的记录集，筛选的是分组。例如HAVING AVG(score)>=80，筛选的是那些平均成绩大于80的分组，而上面的HAVING salary=MAX(s.salary)实际上是想筛选分组中的记录，这是错误的。

正确实例

这篇博文里的所有正确实例均不是唯一解，只是提供一种思路，具体写法因人而异！

select r.dept_no,r.emp_no,max(r.salary) from (
select d.dept_no,d.emp_no,s.salary from dept_emp d,salaries s
where d.emp_no=s.emp_no
and d.to_date='9999-01-01' 
and s.to_date='9999-01-01'
order by s.salary desc )as r
group by r.dept_no

第二题

来源：佰钧成面试题

写一个查询语句，规定考勤日期为‘2020-02-16’，输出表格如下：
注：
1、2020-02-16李四派工期已结束，不在派工期间不需要计算考勤，故不用显示
2、赵六在当日没有打卡，按照迟到算，迟到时间1440分钟

姓名	考勤日期	是否迟到	迟到时间
张三	2020-02-16	否	0
王五	2020-02-16	是	63
赵刘	2020-02-16	是	1440

题目提供的建表语句与数据：

create table work_plan(
	worker_name varchar(10),
	start_date date,
	end_date date,
	sign_time varchar(10)
);

insert into work_plan values
('张三','2020-01-01',null,'06:30'),
('李四','2020-02-01','2020-02-15','07:00'),
('王五','2019-12-29','2020-03-30','06:00'),
('赵六','2019-12-29','2020-03-30','06:00')

create table sign_log(
worker_name varchar(10),
sign_time datetime
);

insert into sign_log values
('张三','2020-02-16 06:03'),
('王五','2020-02-16 08:03'),

其他条件：

1、表示某人从某日开始到某日结束，按要求工作，派工期间每日打卡时间必须在“要求到岗时间”前（含要求时间，精确到分钟），否则迟到。例如：要求7：00，则6:59或者7:00:59 都不算迟到；7:01则视为迟到1分钟

2、行1中“派工结束日期”为null，表示此人的工作结束时间尚未确定，还在搬砖中；行2中派工结束日期为2020-02-15，表示派工于02-15日结束。

3、假设员工名字不重复，每人只有一条派工信息

要点分析

这道题要求的限制条件很多很多，需要一点点分析，建议分几个小部分先写出来，最后整合成一条语句；另外还需要熟知判断语句的格式与运用、一些和日期相关的函数

目前博主还没能力写出自定义函数，所以基本上只会使用子查询和join

• 为了满足第二个条件，写出语句FROM work_plan WHERE end_date > '2020-02-16' OR end_date IS NULL

• 筛选出满足第二个条件的员工后，获取他们的姓名，手动赋值他们当天理当的打卡时间和考勤日期SELECT worker_name, '2020-02-16 07:00:00' sign_time, '2020-02-16' AS query_date FROM...

• “赵六在当日没有打卡，按照迟到算，迟到时间1440分钟”，现在我们要找出那个没有打卡的员工。这里我们需要用到left join找到左连接后worker_name为空的人，并且将他的打卡日期设为明天的早上7点（即迟到1440分钟）这里不是修改表里的数据，而是方便计算

• 到这里我们已经把所有应当打卡的员工（不包括工作期已结束的）的要求打卡情况和实际打卡情况都放在一张表里了，现在我们只需要将要求和实际比较，并用TIMESTAMPDIFF函数求出差值

正确实例

SELECT
t2.worker_name,
t2.query_date AS attend,
IF (t2.start_sign_time < t2.sign_time, '否', '是' ) AS isLate,
IF (t2.start_sign_time < t2.sign_time, 0, TIMESTAMPDIFF( MINUTE, t2.sign_time, t2.start_sign_time ) ) AS latetime
FROM (
	SELECT
	t.*,
	IF (t1.worker_name IS NULL, DATE_ADD(t.sign_time, INTERVAL 1 DAY), t1.sign_time) start_sign_time
	FROM (
		SELECT
		worker_name,
		'2020-02-16 07:00:00' sign_time,
		'2020-02-16' AS query_date
		FROM
			work_plan
			WHERE
			end_date > '2020-02-16' OR end_date IS NULL
		) t
	LEFT JOIN sign_log t1 ON t.worker_name = t1.worker_name
	GROUP BY t.worker_name
) t2

第三题