sqlsqlsqlsql测试

create table swipe
(
    stu_id     int          not null,
    name       varchar(100) null,
    swipe_date varchar(100) null,
    swipe_time varchar(100) null
);

INSERT INTO test.swipe (stu_id, name, swipe_date, swipe_time) VALUES (1, '张三', '20200601', '20200601100230');
INSERT INTO test.swipe (stu_id, name, swipe_date, swipe_time) VALUES (1, '张三', '20200601', '20200601181030');
INSERT INTO test.swipe (stu_id, name, swipe_date, swipe_time) VALUES (2, '李四', '20200602', '20200602082212');
INSERT INTO test.swipe (stu_id, name, swipe_date, swipe_time) VALUES (1, '张三', '20200602', '20200602180622');


create table tb_time
(
    date_str varchar(100) not null
        primary key
);

INSERT INTO test.tb_time (date_str) VALUES ('20200601');
INSERT INTO test.tb_time (date_str) VALUES ('20200602');
INSERT INTO test.tb_time (date_str) VALUES ('20200603');

select * from swipe order by stu_id,swipe_date;

# select *
# from tb_time a
#          left join swipe b on a.date_str = b.swipe_date;

select * from ( select stu_id,name  from swipe group by stu_id,name) a ,tb_time;



/*实现了 最终版*/
select a.*,b.swipe_time from (
select *
from (select stu_id, name from swipe group by stu_id, name) a,
     tb_time) a left join swipe b on a.date_str = b.swipe_date and a.stu_id=b.stu_id order by a.stu_id,a.date_str;