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

原表的数据如下

img

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

img

这样SQL语句要怎么写？


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;

使用开窗函数进行序号分组列出，然后用列转行，将分组序号与字段名结合


with t as (
    select '002007' card,'01' rq,'07:24' sktime
    union all select '002007','01','11:46'
    union all select '002007','01','11:48'
    union all select '002007','01','17:02'
    union all select '009004','01','07:58'
    union all select '009004','01','11:32'
    union all select '009004','01','12:02'
    union all select '009004','01','17:02'
)
select card,rq,[1] sktime1,[2] sktime2,[3] sktime3,[4] sktime4 
from (
    select *,row_number() over(partition by card order by sktime) num 
    from t
) a
pivot(max(sktime) for num in ([1],[2],[3],[4])) p

该回答引用ChatGPT
SQL 预计如下

WITH original_data AS (
  SELECT '2007' AS card, '1' AS rq, '7:24' AS sktime UNION
  SELECT '2007' AS card, '1' AS rq, '7:58' AS sktime UNION
  SELECT '2007' AS card, '1' AS rq, '11:46' AS sktime UNION
  SELECT '2007' AS card, '1' AS rq, '11:48' AS sktime UNION
  SELECT '2007' AS card, '1' AS rq, '17:02' AS sktime UNION
  SELECT '9004' AS card, '1' AS rq, '11:32' AS sktime UNION
  SELECT '9004' AS card, '1' AS rq, '12:02' AS sktime UNION
  SELECT '9004' AS card, '1' AS rq, '17:02' AS sktime 
), 
pivot_data AS (
  SELECT card, rq, 
         MIN(CASE WHEN sktime='7:24' THEN sktime END) AS sktime1, 
         MIN(CASE WHEN sktime='7:58' THEN sktime END) AS sktime2, 
         MIN(CASE WHEN sktime='11:46' THEN sktime END) AS sktime3, 
         MIN(CASE WHEN sktime='11:48' THEN sktime END) AS sktime4, 
         MIN(CASE WHEN sktime='11:32' THEN sktime END) AS sktime5, 
         MIN(CASE WHEN sktime='12:02' THEN sktime END) AS sktime6, 
         MIN(CASE WHEN sktime='17:02' THEN sktime END) AS sktime7
  FROM original_data
  GROUP BY card, rq
)
SELECT card, rq, sktime1, sktime2, sktime3, sktime4, sktime5, sktime6, sktime7
FROM pivot_data

用的哪种数据库?