基于SQL实现CheckTime表用户每日签到签退时间统计

先明确你的需求：基于CheckTime表，按用户名和日期分组，将每组里最早的CheckTime作为签到时间（check in），最晚的作为签退时间（check out）。

先回顾下你的表结构：

CREATE TABLE [dbo].[CheckTime] (
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [UserName] [nchar](10) NULL,
 [CheckTime] [datetime] NULL,
 [Checktype] [nvarchar](50) NULL,
 [CheckinLocation] [nvarchar](50) NULL,
 [lat] [float] NULL,
 [lng] [float] NULL,
 CONSTRAINT [PK_CheckTime] PRIMARY KEY CLUSTERED ([ID] ASC) WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

基础版：仅获取签到签退时间 #

你可以使用GROUP BY结合日期函数提取日期部分，再用MIN()和MAX()聚合函数分别获取签到和签退时间：

SELECT
    [UserName],
    CONVERT(date, [CheckTime]) AS [CheckDate],
    MIN([CheckTime]) AS [CheckInTime],
    MAX([CheckTime]) AS [CheckOutTime]
FROM [dbo].[CheckTime]
WHERE [UserName] IS NOT NULL AND [CheckTime] IS NOT NULL -- 过滤无效数据
GROUP BY [UserName], CONVERT(date, [CheckTime])
ORDER BY [CheckDate] DESC, [UserName]

代码解释

• CONVERT(date, [CheckTime])：把datetime类型的CheckTime转换为date类型，这样就能按自然日分组，忽略具体时分秒。
• MIN([CheckTime])：取每组中最早的时间，也就是签到时间。
• MAX([CheckTime])：取每组中最晚的时间，也就是签退时间。
• WHERE子句：过滤掉用户名或时间为空的无效记录，避免分组出现异常结果。
• ORDER BY：按日期倒序、用户名排序，方便查看最新的打卡记录。

进阶版：获取签到签退对应的地点和经纬度 #

如果需要同时拿到签到/签退对应的打卡地点、经纬度，单纯的聚合函数没法直接关联这些字段，这时候可以用窗口函数ROW_NUMBER()来实现：

WITH RankedChecks AS (
    SELECT
        [UserName],
        CONVERT(date, [CheckTime]) AS [CheckDate],
        [CheckTime],
        [CheckinLocation],
        [lat],
        [lng],
        -- 按用户和日期分组，时间升序排序，第一条就是签到记录
        ROW_NUMBER() OVER (PARTITION BY [UserName], CONVERT(date, [CheckTime]) ORDER BY [CheckTime] ASC) AS CheckInRank,
        -- 按用户和日期分组，时间降序排序，第一条就是签退记录
        ROW_NUMBER() OVER (PARTITION BY [UserName], CONVERT(date, [CheckTime]) ORDER BY [CheckTime] DESC) AS CheckOutRank
    FROM [dbo].[CheckTime]
    WHERE [UserName] IS NOT NULL AND [CheckTime] IS NOT NULL
)
SELECT
    rc1.[UserName],
    rc1.[CheckDate],
    rc1.[CheckTime] AS [CheckInTime],
    rc1.[CheckinLocation] AS [CheckInLocation],
    rc1.[lat] AS [CheckInLat],
    rc1.[lng] AS [CheckInLng],
    rc2.[CheckTime] AS [CheckOutTime],
    rc2.[CheckinLocation] AS [CheckOutLocation],
    rc2.[lat] AS [CheckOutLat],
    rc2.[lng] AS [CheckOutLng]
FROM RankedChecks rc1
JOIN RankedChecks rc2
    ON rc1.[UserName] = rc2.[UserName]
    AND rc1.[CheckDate] = rc2.[CheckDate]
    AND rc1.CheckInRank = 1
    AND rc2.CheckOutRank = 1
ORDER BY rc1.[CheckDate] DESC, rc1.[UserName]

这个版本能完整获取每个用户每天的签到、签退时间及对应的打卡地点、经纬度，适合需要完整打卡明细的业务场景。

内容的提问来源于stack exchange，提问作者emadadel2008