如何在同一张表中获取同一identifier_id的最新Limit1和Limit2值

搞定这个问题其实不难，核心就是把同一identifier_id下最新时间戳对应的Limit1、Limit2参数从行转成列，同时保留你需要的id（这里取Limit1对应的id，和你期望的结果一致）。下面提供几种通用的SQL方案，适配大多数主流数据库（MySQL、PostgreSQL、SQL Server等）：

方案一：窗口函数+条件聚合（推荐，灵活度高） #

通过窗口函数给每个参数的记录按时间排序标记，再用条件聚合把多行合并成一行：

WITH ranked_records AS (
    SELECT 
        id,
        identifier_id,
        name,
        int_value,
        -- 给每个identifier_id下的同名称参数按时间倒序排名，最新的排第1
        ROW_NUMBER() OVER (PARTITION BY identifier_id, name ORDER BY ts DESC) AS record_rank
    FROM your_table_name
    WHERE identifier_id = 12 -- 若要处理所有identifier_id，删除此条件即可
)
SELECT 
    -- 取Limit1对应的id作为结果行的id
    MAX(CASE WHEN name = 'Limit1' THEN id END) AS id,
    identifier_id,
    -- 提取Limit1的最新值
    MAX(CASE WHEN name = 'Limit1' THEN int_value END) AS Limit1_value,
    -- 提取Limit2的最新值
    MAX(CASE WHEN name = 'Limit2' THEN int_value END) AS Limit2_value
FROM ranked_records
-- 只保留每个参数的最新记录
WHERE record_rank = 1 AND name IN ('Limit1', 'Limit2')
GROUP BY identifier_id;

方案二：子查询找最新时间+关联筛选 #

如果你的数据库不支持窗口函数（比如老版本MySQL），可以用这个方案：

SELECT 
    MAX(CASE WHEN t.name = 'Limit1' THEN t.id END) AS id,
    t.identifier_id,
    MAX(CASE WHEN t.name = 'Limit1' THEN t.int_value END) AS Limit1_value,
    MAX(CASE WHEN t.name = 'Limit2' THEN t.int_value END) AS Limit2_value
FROM your_table_name t
-- 关联子查询找到identifier_id=12的最新时间戳
INNER JOIN (
    SELECT identifier_id, MAX(ts) AS latest_ts
    FROM your_table_name
    WHERE identifier_id = 12
    GROUP BY identifier_id
) latest_time 
    ON t.identifier_id = latest_time.identifier_id 
    AND t.ts = latest_time.latest_ts
WHERE t.name IN ('Limit1', 'Limit2')
GROUP BY t.identifier_id;

方案三：PIVOT语法（适用于支持的数据库，如SQL Server、Oracle） #

如果你的数据库原生支持PIVOT，可以简化行转列的操作：

WITH latest_data AS (
    SELECT id, identifier_id, name, int_value
    FROM your_table_name
    WHERE identifier_id = 12
    -- 筛选出最新时间戳的记录
    AND ts = (SELECT MAX(ts) FROM your_table_name WHERE identifier_id = 12)
    AND name IN ('Limit1', 'Limit2')
)
SELECT 
    MAX(CASE WHEN name = 'Limit1' THEN id END) AS id,
    identifier_id,
    Limit1 AS Limit1_value,
    Limit2 AS Limit2_value
FROM latest_data
-- 将name列的值转成列，对应int_value的值
PIVOT (
    MAX(int_value) FOR name IN ('Limit1' AS Limit1, 'Limit2' AS Limit2)
) AS pivot_table
GROUP BY identifier_id, Limit1, Limit2;

小提示 #

• 记得把your_table_name替换成你实际的表名
• 如果需要批量处理所有identifier_id，只需删除所有WHERE identifier_id = 12的条件，每个identifier_id都会返回对应最新的Limit1和Limit2值

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