题目：

从一张考勤表TAB中找出员工每天的上班，下班打卡的具体时间？

考勤表中相关字段如下：ID，NAME，NO，TIME

image

其中ID是主键，NAME为员工姓名，NO为工号，TIME为打卡时间

返回的结果如下：

image

参考答案：

数据库版本：Server version: 8.0.20 MySQL Community Server - GPL

建表语句

create table dailytest_20200609
(
    id        int,
    name      varchar(20),
    num       int,
    work_time timestamp
);

数据准备

insert into dailytest_20200609 values (1,'张三',1001,'2019-11-09 08:22:27');
insert into dailytest_20200609 values (2,'李四',1002,'2019-11-09 08:08:52');
insert into dailytest_20200609 values (3,'张三',1001,'2019-11-09 12:13:16');
insert into dailytest_20200609 values (4,'李四',1002,'2019-11-09 18:09:22');
insert into dailytest_20200609 values (5,'王五',1003,'2019-11-09 08:22:27');
insert into dailytest_20200609 values (6,'张三',1001,'2019-11-09 18:31:14');

查询逻辑

select
       date(work_time) as '日期',
       name as '姓名',
       min(work_time) as '上班时间',
       max(work_time) as '下班时间'
from dailytest_20200609
    group by
        num,
        name,
        date(work_time);

附：
题目来源：https://mp.weixin.qq.com/s/krzPpna7GA1qv6M3lTWZmg