如何用单条SQL语句获取两个表中最新的自定义值？

当然可以用单条SQL完成这个需求，核心思路是先分别提取两个表中最新的记录，再从这两个结果里筛选出时间戳最晚的那个值。

基础查询（获取最新值） #

以下是通用的SQL写法，适用于大多数支持LIMIT的数据库（如MySQL、PostgreSQL）：

SELECT customValue
FROM (
    -- 从TableA取最新的customValueA和对应时间戳
    SELECT customValueA AS customValue, timestamp
    FROM TableA
    ORDER BY timestamp DESC
    LIMIT 1
    -- 合并TableB的最新记录
    UNION ALL
    SELECT customValueB AS customValue, timeStamp
    FROM TableB
    ORDER BY timeStamp DESC
    LIMIT 1
) AS latest_values
-- 从两个最新记录里取时间戳最晚的那个
ORDER BY timestamp DESC
LIMIT 1;

赋值给变量 #

如果要把结果直接赋值给变量，不同数据库的语法略有区别：

• MySQL：

  SET @customValue = (
      SELECT customValue
      FROM (
          SELECT customValueA AS customValue, timestamp
          FROM TableA
          ORDER BY timestamp DESC
          LIMIT 1
          UNION ALL
          SELECT customValueB AS customValue, timeStamp
          FROM TableB
          ORDER BY timeStamp DESC
          LIMIT 1
      ) AS latest_values
      ORDER BY timestamp DESC
      LIMIT 1
  );
  

• SQL Server（使用OFFSET FETCH替代LIMIT）：

  DECLARE @customValue VARCHAR(255); -- 替换成你的字段实际数据类型
  SET @customValue = (
      SELECT customValue
      FROM (
          SELECT customValueA AS customValue, timestamp
          FROM TableA
          ORDER BY timestamp DESC
          OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
          UNION ALL
          SELECT customValueB AS customValue, timeStamp
          FROM TableB
          ORDER BY timeStamp DESC
          OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
      ) AS latest_values
      ORDER BY timestamp DESC
      OFFSET 0 ROWS FETCH NEXT 1 ROWS ONLY
  );
  

注意事项 #

• 你提到的两个表时间戳字段名分别是timestamp和timeStamp，注意和实际表结构保持一致，避免大小写或拼写错误。
• 如果你的数据库支持窗口函数（如MySQL 8+、PostgreSQL），也可以用ROW_NUMBER()窗口函数实现，但上面的写法更直观，适合新手理解。

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