SQL如何将同一SessionId的多行TimeStamp数据拆分为多列查询

前提说明 #

假设原始业务表名为session_records，核心包含SessionId、TimeStamp两个关键字段，可根据实际表结构调整字段名。

方案1：固定列数通用实现（兼容所有支持窗口函数的SQL版本） #

适合提前知晓单个SessionId对应的TimeStamp最大数量的场景，语法兼容MySQL、PostgreSQL、SQL Server等绝大多数主流数据库

SELECT 
  SessionId,
  MAX(CASE WHEN rn = 1 THEN TimeStamp END) AS TimeStamp1,
  MAX(CASE WHEN rn = 2 THEN TimeStamp END) AS TimeStamp2,
  MAX(CASE WHEN rn = 3 THEN TimeStamp END) AS TimeStamp3,
  -- 有更多列需求可按相同规则继续追加
  MAX(CASE WHEN rn = N THEN TimeStamp END) AS TimeStampN
FROM (
  SELECT 
    SessionId,
    TimeStamp,
    -- 同SessionId下按时间正序排序编号，可按需调整排序规则
    ROW_NUMBER() OVER (PARTITION BY SessionId ORDER BY TimeStamp) AS rn
  FROM session_records
) t
GROUP BY SessionId;

方案2：动态列实现（适配任意数量TimeStamp，无需提前定义列数） #

MySQL 8.0+ 版本实现 #

SET @sql = NULL;
-- 动态生成所有时间戳列的查询逻辑
SELECT GROUP_CONCAT(DISTINCT
  CONCAT('MAX(CASE WHEN rn = ', rn, ' THEN TimeStamp END) AS TimeStamp', rn)
) INTO @sql
FROM (
  SELECT ROW_NUMBER() OVER (PARTITION BY SessionId ORDER BY TimeStamp) AS rn
  FROM session_records
) t;

-- 拼接完整查询语句
SET @sql = CONCAT('SELECT SessionId, ', @sql, ' FROM (
  SELECT SessionId, TimeStamp, ROW_NUMBER() OVER (PARTITION BY SessionId ORDER BY TimeStamp) AS rn
  FROM session_records
) t GROUP BY SessionId');

-- 执行动态查询
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

PostgreSQL 版本实现 #

DO $$
DECLARE
    cols text;
    query text;
BEGIN
    -- 动态生成列查询逻辑
    SELECT string_agg(DISTINCT format('MAX(CASE WHEN rn = %s THEN "TimeStamp" END) AS "TimeStamp%s"', rn, rn), ', ')
    INTO cols
    FROM (
        SELECT ROW_NUMBER() OVER (PARTITION BY "SessionId" ORDER BY "TimeStamp") AS rn
        FROM session_records
    ) t;

    -- 拼接完整查询语句并执行
    query := format('SELECT "SessionId", %s FROM (
        SELECT "SessionId", "TimeStamp", ROW_NUMBER() OVER (PARTITION BY "SessionId" ORDER BY "TimeStamp") AS rn
        FROM session_records
    ) t GROUP BY "SessionId"', cols);
    EXECUTE query;
END $$;

注意事项 #

• 排序规则可按需调整，若需要按时间倒序排列列内容，把窗口函数中的ORDER BY TimeStamp改为ORDER BY TimeStamp DESC即可
• 若原始表中存在同SessionId下值统一的其他字段（比如用户ID），可直接放在外层SELECT和GROUP BY语句中同步返回

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