SQLite按EventType拆分Datetime为Logon/Logoff列的实现方法

针对将包含User、EventType、Time字段的日志表，转换为以用户为唯一标识、包含登录/登出时间的新表需求，提供两种常用SQL实现方案：

方案一：条件聚合（推荐） #

利用CASE条件筛选结合分组聚合，简洁高效，能自动处理用户仅存在登录或登出记录的情况（对应字段返回NULL）。

假设原表名为event_log，创建新表的SQL语句如下：

CREATE TABLE user_session AS
SELECT
  User,
  MAX(CASE WHEN EventType = 'Logon' THEN Time END) AS Logon,
  MAX(CASE WHEN EventType = 'Logoff' THEN Time END) AS Logoff
FROM event_log
GROUP BY User;

• 若仅需查询结果而非创建新表，移除CREATE TABLE user_session AS直接执行SELECT语句即可。
• 若用户存在多条登录/登出记录，MAX会取该用户最晚的登录/登出时间；如需最早时间，替换为MIN即可。

方案二：自连接 #

通过拆分登录、登出子表再关联的方式实现，适合确认每个用户恰好有一对登录/登出记录的场景。

SQL语句示例：

CREATE TABLE user_session AS
SELECT
  l.User,
  l.Time AS Logon,
  o.Time AS Logoff
FROM (SELECT User, Time FROM event_log WHERE EventType = 'Logon') l
JOIN (SELECT User, Time FROM event_log WHERE EventType = 'Logoff') o
ON l.User = o.User;

• 若需保留仅存在登录或登出记录的用户，将JOIN改为LEFT JOIN，并通过COALESCE确保User字段不为空；由于SQLite不支持FULL OUTER JOIN，可通过UNION补全另一侧的缺失记录：

CREATE TABLE user_session AS
SELECT
  l.User,
  l.Time AS Logon,
  o.Time AS Logoff
FROM (SELECT User, Time FROM event_log WHERE EventType = 'Logon') l
LEFT JOIN (SELECT User, Time FROM event_log WHERE EventType = 'Logoff') o
ON l.User = o.User
UNION
SELECT
  o.User,
  l.Time AS Logon,
  o.Time AS Logoff
FROM (SELECT User, Time FROM event_log WHERE EventType = 'Logoff') o
LEFT JOIN (SELECT User, Time FROM event_log WHERE EventType = 'Logon') l
ON o.User = l.User
WHERE l.User IS NULL;

内容的提问来源于stack exchange，提问作者BM-SMS