SQL 表中 考勤记录的组合查询

Question

原表的数据如下

这个表1中的数据需要SQL语句查询成如下格式

这样SQL语句要怎么写？

Answer 1

SELECT 
  card, 
  COUNT(DISTINCT rq) AS re, 
  MIN(CASE WHEN sktime BETWEEN '07:00' AND '08:00' THEN sktime END) AS skimt1, 
  MIN(CASE WHEN sktime BETWEEN '11:00' AND '12:00' THEN sktime END) AS skimt2, 
  MIN(CASE WHEN sktime BETWEEN '11:45' AND '13:00' THEN sktime END) AS skimt3, 
  MIN(CASE WHEN sktime BETWEEN '17:00' AND '18:00' THEN sktime END) AS skimt4 
FROM 
  table1 
GROUP BY 
  card;