SELECT MIN(id)[编号] ,CONVERT(VARCHAR(10),DATEADD(HOUR,-8,date),120)[日期] ,[USER][用户] ,MIN(date)[第一次记录] ,MAX(date)[最后一次记录] ,STUFF((SELECT','+CONVERT(VARCHAR(30),date,120)FROM TB WHERE CONVERT(VARCHAR(10),DATEADD(HOUR,-8,date),120)=CONVERT(VARCHAR(10),DATEADD(HOUR,-8,T.date),120) AND [USER]=T.[USER] FOR XML PATH('')),1,1,'')[当天刷卡详细记录] FROM TB T GROUP BY CONVERT(VARCHAR(10),DATEADD(HOUR,-8,date),120),[USER]
--逻辑:1、根据日期,用户,汇总最小,最大值 ;with cte as( select left([date],9) as ldate, [user],min(date) as mindate, max(date) as maxdate from tb group by left([date],9), [user] ), --2、根据用户,当天,获取详细的打卡记录 cteRow as( select 0 as n,ldate,[user],mindate,maxdate, cast('|' as varchar(max)) as detailDate --详细打卡的时段,如08:07:57|08:08:53 from cte union all select n+1,ldate,[user],mindate,maxdate, cteRow.detailDate+cast(right(tb.date,8) as varchar(max))+'|' from cteRow join tb on tb.[user]=cteRow.[user] and cteRow.ldate=left(tb.date,9) )select * from cteRow order by n
,CONVERT(VARCHAR(10),DATEADD(HOUR,-8,date),120)[日期]
,[USER][用户]
,MIN(date)[第一次记录]
,MAX(date)[最后一次记录]
,STUFF((SELECT','+CONVERT(VARCHAR(30),date,120)FROM TB
WHERE CONVERT(VARCHAR(10),DATEADD(HOUR,-8,date),120)=CONVERT(VARCHAR(10),DATEADD(HOUR,-8,T.date),120)
AND [USER]=T.[USER] FOR XML PATH('')),1,1,'')[当天刷卡详细记录]
FROM TB T
GROUP BY CONVERT(VARCHAR(10),DATEADD(HOUR,-8,date),120),[USER]
;with cte as(
select left([date],9) as ldate, [user],min(date) as mindate,
max(date) as maxdate
from tb
group by left([date],9), [user]
),
--2、根据用户,当天,获取详细的打卡记录
cteRow as(
select 0 as n,ldate,[user],mindate,maxdate,
cast('|' as varchar(max)) as detailDate --详细打卡的时段,如08:07:57|08:08:53
from cte
union all
select n+1,ldate,[user],mindate,maxdate,
cteRow.detailDate+cast(right(tb.date,8) as varchar(max))+'|'
from cteRow
join tb
on tb.[user]=cteRow.[user] and cteRow.ldate=left(tb.date,9)
)select * from cteRow
order by n