MySQL语法困惑：多表取最新数据合并插入t_snapshots的实现问题

实现逻辑 #

• 第一步：提取t_other_data0中每个value_type对应最新timestamp的ch值记录
• 第二步：提取t_id_pool中每个channel_num对应最新timestamp的unit_id，转置为ch1_id至ch4_id的宽表格式
• 第三步：关联两份结果，写入目标表t_snapshots

MySQL 8.0+ 实现代码（支持窗口函数） #

INSERT INTO t_snapshots (value_type, ch1_id, ch1_val, ch2_id, ch2_val, ch3_id, ch3_val, ch4_id, ch4_val, timestamp)
WITH 
-- 子查询1：取每个value_type最新的业务数据
latest_ch_data AS (
    SELECT value_type, ch1, ch2, ch3, ch4, timestamp
    FROM (
        SELECT *,
               ROW_NUMBER() OVER (PARTITION BY value_type ORDER BY timestamp DESC) AS rn
        FROM t_other_data0
    ) t
    WHERE rn = 1
),
-- 子查询2：取每个channel最新的unit_id，转置为宽表
latest_id_data AS (
    SELECT
        MAX(CASE WHEN channel_num = 1 THEN unit_id END) AS ch1_id,
        MAX(CASE WHEN channel_num = 2 THEN unit_id END) AS ch2_id,
        MAX(CASE WHEN channel_num = 3 THEN unit_id END) AS ch3_id,
        MAX(CASE WHEN channel_num = 4 THEN unit_id END) AS ch4_id
    FROM (
        SELECT channel_num, unit_id,
               ROW_NUMBER() OVER (PARTITION BY channel_num ORDER BY timestamp DESC) AS rn
        FROM t_id_pool
    ) t
    WHERE rn = 1
)
-- 关联两份数据写入目标表
SELECT lcd.value_type, lid.ch1_id, lcd.ch1, lid.ch2_id, lcd.ch2, lid.ch3_id, lcd.ch3, lid.ch4_id, lcd.ch4, lcd.timestamp
FROM latest_ch_data lcd
CROSS JOIN latest_id_data lid;

MySQL 5.x 兼容实现（无窗口函数） #

INSERT INTO t_snapshots (value_type, ch1_id, ch1_val, ch2_id, ch2_val, ch3_id, ch3_val, ch4_id, ch4_val, timestamp)
SELECT 
    lcd.value_type, lid.ch1_id, lcd.ch1, lid.ch2_id, lcd.ch2, lid.ch3_id, lcd.ch3, lid.ch4_id, lcd.ch4, lcd.timestamp
FROM
-- 子查询1：取每个value_type最新业务数据
(
    SELECT t1.*
    FROM t_other_data0 t1
    INNER JOIN (
        SELECT value_type, MAX(timestamp) AS max_ts
        FROM t_other_data0
        GROUP BY value_type
    ) t2 ON t1.value_type = t2.value_type AND t1.timestamp = t2.max_ts
) lcd
CROSS JOIN 
-- 子查询2：取每个channel最新unit_id转宽表
(
    SELECT
        MAX(CASE WHEN channel_num = 1 THEN unit_id END) AS ch1_id,
        MAX(CASE WHEN channel_num = 2 THEN unit_id END) AS ch2_id,
        MAX(CASE WHEN channel_num = 3 THEN unit_id END) AS ch3_id,
        MAX(CASE WHEN channel_num = 4 THEN unit_id END) AS ch4_id
    FROM t_id_pool t1
    INNER JOIN (
        SELECT channel_num, MAX(timestamp) AS max_ts
        FROM t_id_pool
        GROUP BY channel_num
    ) t2 ON t1.channel_num = t2.channel_num AND t1.timestamp = t2.max_ts
) lid;

注意：如果同一value_type存在多条相同最大timestamp的记录，可根据业务需要调整排序逻辑，增加id排序避免重复

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