如何基于ROW_NUMBER()高效生成多列？避免逐列编写代码

我创建了TBL表并插入测试数据，使用ROW_NUMBER()函数按ID分区、DATE排序后，通过多次左连接生成了DATE1、DATE2、DATE3三列。但当需要扩展生成更多列时，不想逐列编写代码，请问是否有更优实现方式？

创建表及插入数据代码 #

CREATE TABLE TBL(
  ID INT NOT NULL,
  DATE DATE
  );
INSERT INTO TBL VALUES (1003, '2022-05-03');
INSERT INTO TBL VALUES (1002, '2022-01-02');
INSERT INTO TBL VALUES (1003, '2022-02-05');
INSERT INTO TBL VALUES (1001, '2022-01-02');
INSERT INTO TBL VALUES (1003, '2022-01-02');
INSERT INTO TBL VALUES (1003, '2022-04-04');
INSERT INTO TBL VALUES (1001, '2022-01-01');
INSERT INTO TBL VALUES (1001, '2022-01-03');
INSERT INTO TBL VALUES (1001, '2022-10-04');
INSERT INTO TBL VALUES (1003, '2022-01-01');
INSERT INTO TBL VALUES (1003, '2022-12-06');
INSERT INTO TBL VALUES (1002, '2022-03-01');

当前实现代码 #

WITH
  DTA AS (
    SELECT
      ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE) AS RN,
      *
    FROM TBL
    )
SELECT DISTINCT
  T.ID,
  T1.DATE AS DATE1,
  T2.DATE AS DATE2,
  T3.DATE AS DATE3
FROM TBL T
LEFT JOIN (SELECT * FROM DTA WHERE RN = 1) T1
ON T1.ID = T.ID
LEFT JOIN (SELECT * FROM DTA WHERE RN = 2) T2
ON T2.ID = T.ID
LEFT JOIN (SELECT * FROM DTA WHERE RN = 3) T3
ON T3.ID = T.ID

当前输出结果 #

方法一：条件聚合（静态列数场景） #

如果需要的列数固定（比如最多到DATE10），用CASE WHEN结合聚合函数直接转换，无需多次连接，代码更简洁高效：

WITH DTA AS (
    SELECT
        ID,
        DATE,
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE) AS RN
    FROM TBL
)
SELECT
    ID,
    MAX(CASE WHEN RN = 1 THEN DATE END) AS DATE1,
    MAX(CASE WHEN RN = 2 THEN DATE END) AS DATE2,
    MAX(CASE WHEN RN = 3 THEN DATE END) AS DATE3,
    -- 按需追加更多列，示例：DATE4
    MAX(CASE WHEN RN = 4 THEN DATE END) AS DATE4
FROM DTA
GROUP BY ID;

只需在SELECT语句中追加MAX(CASE...)即可扩展列，逻辑清晰且性能优于多次左连接。

方法二：动态SQL（动态列数场景） #

如果需要根据数据自动生成所有所需列（比如某ID有10条数据就生成DATE1到DATE10），可以用动态SQL自动拼接列名：

SQL Server 实现

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);

-- 生成所有DATE列名
SELECT @cols = STRING_AGG(QUOTENAME('DATE' + CAST(RN AS VARCHAR)), ', ')
FROM (
    SELECT DISTINCT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE) AS RN
    FROM TBL
) t
WHERE RN > 0;

-- 拼接并执行完整SQL
SET @sql = N'
WITH DTA AS (
    SELECT
        ID,
        DATE,
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE) AS RN
    FROM TBL
)
SELECT ID, ' + @cols + N'
FROM DTA
PIVOT (
    MAX(DATE)
    FOR RN IN (' + REPLACE(@cols, 'DATE', '') + N')
) p;';

EXEC sp_executesql @sql;

MySQL 实现

-- 生成列名列表
SET @cols = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN RN = ', RN, ' THEN DATE END) AS DATE', RN)) INTO @cols
FROM (
    SELECT ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE) AS RN
    FROM TBL
) t;

-- 拼接并执行SQL
SET @sql = CONCAT('
WITH DTA AS (
    SELECT
        ID,
        DATE,
        ROW_NUMBER() OVER(PARTITION BY ID ORDER BY DATE) AS RN
    FROM TBL
)
SELECT ID, ', @cols, '
FROM DTA
GROUP BY ID;');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

动态SQL会自动根据数据中最大的RN值生成对应DATE列，无需手动修改代码，适合列数不固定的场景。

内容的提问来源于stack exchange，提问作者T. Müller