基于条件创建SQL变量：计算用户会话时长遇问题求助

问题分析 #

1. 数据类型错误：time类型不支持直接减法运算，必须先转换为datetime2类型计算时间差，再转换回合适的时长格式。
2. 字段设计不合理：原session_time设为date类型完全不符合会话时长的存储需求，应改为time（适用于不超过24小时的会话）或int（存储秒数，支持任意时长）。
3. 无logout记录的处理缺失：未对无logout记录的用户提取当日最后操作时间作为logout时间。
4. 冗余过滤条件：原UPDATE语句中的username = username AND date = date是恒真条件，无实际过滤作用。

具体步骤 #

1. 修正表字段

先删除错误添加的字段，重新创建合适的会话时长字段：

-- 删除错误字段（若已添加）
ALTER TABLE ##remote_users DROP COLUMN session_time, login_time, logout_time;

-- 添加会话时长字段，这里推荐用time类型（会话不超24小时）或int存储秒数（更灵活）
ALTER TABLE ##remote_users ADD session_time time;
-- 可选：若会话可能超过24小时，改用秒数存储
-- ALTER TABLE ##remote_users ADD session_duration_seconds int;

2. 计算并更新会话时长

使用CTE（公共表表达式）先按用户+日期聚合，获取login时间、实际logout/最后操作时间，再计算时长并更新原表：

WITH UserDailySessions AS (
    SELECT
        date,
        username,
        -- 获取当日用户的login完整时间戳
        MAX(CASE WHEN type = 'login' THEN CONVERT(datetime2, date) + CONVERT(datetime2, time) END) AS login_datetime,
        -- 优先取logout时间，无则取当日最后操作时间
        COALESCE(
            MAX(CASE WHEN type = 'logout' THEN CONVERT(datetime2, date) + CONVERT(datetime2, time) END),
            MAX(CONVERT(datetime2, date) + CONVERT(datetime2, time))
        ) AS logout_datetime
    FROM ##remote_users
    GROUP BY date, username
)
-- 更新原表的session_time字段
UPDATE ru
SET 
    ru.session_time = CAST(
        DATEADD(SECOND, 
            DATEDIFF(SECOND, uds.login_datetime, uds.logout_datetime),
            0
        ) AS time
    ),
    -- 若用秒数存储则替换为下面一行
    -- ru.session_duration_seconds = DATEDIFF(SECOND, uds.login_datetime, uds.logout_datetime)
FROM ##remote_users ru
JOIN UserDailySessions uds 
    ON ru.date = uds.date AND ru.username = uds.username;

3. 验证结果

执行查询查看更新后的会话时长：

SELECT 
    date,
    time,
    username,
    type,
    session_time
FROM ##remote_users
ORDER BY username, date, time;

补充说明 #

• 若用户当日存在多次登录（多个login记录），上述SQL会取当日最晚的login时间和对应的logout/最后操作时间。如果需要按login-logout配对（如每个login后匹配下一个logout），需调整CTE逻辑，使用窗口函数（如ROW_NUMBER()）进行会话分组。
• 当会话时长超过24小时时，time类型无法存储完整时长，此时建议使用int类型存储秒数，或用varchar存储格式化的时长文本（如'25:30:15'）。

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