sql - 如何从 PostgreSQL 子查询返回两个值？

Question

我遇到了一个问题，我需要在 PostgreSQL 的各个表中获取最后一项。

以下代码有效并返回最新更新的类型以及上次更新的时间。

问题是，这个查询需要用作子查询，所以我想从这个查询中选择类型和最后更新的值，而 PostgreSQL 似乎不喜欢这样......(子查询必须只返回一列)

有什么建议吗？

SELECT last.type, last.max FROM (
    SELECT MAX(a.updated_at), 'a' AS type FROM table_a a WHERE a.ref = 5 UNION
    SELECT MAX(b.updated_at), 'b' AS type FROM table_b b WHERE b.ref = 5
) AS last ORDER BY max LIMIT 1

在 CTE 中这样使用查询；

WITH sql_query as (
    SELECT id, name, address, (...other columns),

    last.type, last.max FROM (
      SELECT MAX(a.updated_at), 'a' AS type FROM table_a a WHERE a.ref = 5 UNION
      SELECT MAX(b.updated_at), 'b' AS type FROM table_b b WHERE b.ref = 5
    ) AS last ORDER BY max LIMIT 1

    FROM table_c
    WHERE table_c.fk_id = 1      
  )

Answer 1

固有的问题是 SQL(所有 SQL 而不仅仅是 Postgres)要求在 select 子句中使用的子查询只能返回一个值。如果您考虑一下该限制，它确实是有道理的。 select 子句返回行和一定数量的列，每个 row.column 位置是网格中的一个位置。您可以通过将连接置于单个位置(或单个“复杂类型”，如 JSON 值)来稍微改变该规则，但无论如何它仍然在该网格中保持单个位置。

但是在这里你确实想要 2 个单独的列并且你需要从同一行返回两列，所以我建议使用 ROW_NUMBER() 而不是 LIMIT 1促进这一点:

WITH LastVals as (
      SELECT type
           , max_date
           , row_number() over(order by max_date DESC) as rn
      FROM (
            SELECT MAX(a.updated_at) AS max_date, 'a' AS type FROM table_a a WHERE a.ref = 5
            UNION ALL
            SELECT MAX(b.updated_at) AS max_date, 'b' AS type FROM table_b b WHERE b.ref = 5
            )
     )
, sql_query as (
     SELECT id
          , name, address, (...other columns)

          , (select type from lastVals where rn = 1) as last_type
          , (select max_date from lastVals where rn = 1) as last_date 

     FROM table_c
     WHERE table_c.fk_id = 1      
    )

----

顺便说一句，在你的子查询中，你应该使用 UNION ALL 类型是一个常量，比如 'a' 或 'b' 那么即使 MAX(a.updated_at)对于 2 个或更多表是相同的，由于类型不同，行仍然是唯一的。 UNION 将尝试删除重复的行，但在这里它无济于事，因此请使用 UNION ALL 避免这种浪费。

----

对于给这只猫蒙皮的另一种方法，请考虑使用 LEFT JOIN 代替

  SELECT id
      , name, address, (...other columns)

      , lastVals.type
      , LastVals.last_date 

  FROM table_c
  WHERE table_c.fk_id = 1      
  LEFT JOIN (
        SELECT type
             , last_date
             , row_number() over(order by last_date DESC) as rn
        FROM (
              SELECT MAX(a.updated_at) AS last_date, 'a' AS type FROM table_a a WHERE a.ref = 5
              UNION ALL
              SELECT MAX(b.updated_at) AS last_date, 'b' AS type FROM table_b b WHERE b.ref = 5
              )
        ) LastVals ON LastVals.rn = 1