求一个考勤 SQL 语句,肯定有大神愿意帮我,多谢

按card_date和user_sn分组,取每天当中第一天数据和最后一条数据
意思就是查询一个月当中每个人每天的第一次刷卡和最后一次刷卡

card_time | user_sn | card_date
2016-11-01 8:20:11 | 0012 | 2016-11-01
2016-11-01 13:45:22 | 0012 | 2016-11-01
2016-11-01 18:35:15 | 0012 | 2016-11-01
2016-11-01 8:20:11 | 0013 | 2016-11-01
2016-11-01 11:45:22 | 0013 | 2016-11-01
2016-11-01 13:45:22 | 0013 | 2016-11-01
2016-11-01 18:35:15 | 0013 | 2016-11-01
2016-11-02 8:20:11 | 0012 | 2016-11-02
2016-11-02 13:45:22 | 0012 | 2016-11-02
2016-11-02 18:35:15 | 0012 | 2016-11-02
2016-11-02 8:20:11 | 0013 | 2016-11-02
2016-11-02 11:45:22 | 0013 | 2016-11-02
2016-11-02 13:45:22 | 0013 | 2016-11-02
2016-11-02 18:35:15 | 0013 | 2016-11-02
2016-11-02 8:35:15 | 0014 | 2016-11-02
2016-11-02 18:26:15 | 0014 | 2016-11-02
2016-11-02 7:11:15 | 0015 | 2016-11-02

取出结果:
card_time | user_sn | card_date
2016-11-01 8:20:11 | 0012 | 2016-11-01
2016-11-01 18:35:15 | 0012 | 2016-11-01
2016-11-01 8:20:11 | 0013 | 2016-11-01
2016-11-01 18:35:15 | 0013 | 2016-11-01
2016-11-02 8:20:11 | 0012 | 2016-11-02
2016-11-02 18:35:15 | 0012 | 2016-11-02
2016-11-02 8:20:11 | 0013 | 2016-11-02
2016-11-02 18:35:15 | 0013 | 2016-11-02
2016-11-02 8:35:15 | 0014 | 2016-11-02
2016-11-02 18:26:15 | 0014 | 2016-11-02
2016-11-02 7:11:15 | 0015 | 2016-11-02

2016-11-02

2016-11-01

8:20:11

18:35:15

3 条回复

enenaaa

2016-11-22 15:53:38 +08:00

SELECT MIN(card_time) AS mintime, MAX(card_time) AS maxtime , user_sn, card_date FROM testtbl GROUP BY user_sn, card_date

2016-11-22 16:10:45 +08:00

@enenaaa 多谢,你这样是可以的,但是我想要的是 min 和 max 分成二条数据显示

enenaaa

2016-11-22 16:16:40 +08:00

@im union all 一下就可以了