SQL如何解决行转列后每个datetime_1对应多条重复行的问题

你编写的SQL存在两处核心逻辑错误：

• 首个CTElab_setup未携带后续需要的item、value字段，也没有筛选出每组id+datetime_1下最早order_datetime对应的行（即行号为1的记录），同时多余的group by会丢失检测项维度数据。
• 第二个CTE的GROUP BY错误包含item、value字段，会强制每个检测项生成独立行，导致聚合后其余字段为null。

WITH lab_rn AS (
    SELECT 
        id,
        datetime_1,
        item,
        value,
        ROW_NUMBER() OVER(PARTITION BY id, datetime_1 ORDER BY order_datetime) AS rn
    FROM data
),
lab_first AS (
    SELECT id, datetime_1, item, value
    FROM lab_rn
    WHERE rn = 1
)
SELECT 
    id,
    datetime_1,
    MAX(CASE WHEN item = 'A' THEN value END) AS a_level,
    MAX(CASE WHEN item = 'B' THEN value END) AS b_level,
    MAX(CASE WHEN item = 'C' THEN value END) AS c_level
FROM lab_first
GROUP BY id, datetime_1

• 第一步开窗排序：对所有记录按id+datetime_1分组，按order_datetime升序排列，最早的检测批次行号标记为1
• 第二步过滤有效数据：仅保留每个分组中行号为1的记录，此时每个id+datetime_1对应3条记录，分别为A、B、C三类检测项的结果
• 第三步行转列聚合：仅按id+datetime_1分组，MAX聚合会自动忽略CASE WHEN生成的null值，将三类检测项的结果合并到同一行

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