PostgreSQL中如何将同一表的两个查询结果合并为新表

现有名为lifelong的表，结构及数据如下：

id           first_meal            last_meal
0   1  2022-07-25 12:28:00  2022-07-25 20:06:00
1   2  2022-07-26 13:12:00  2022-07-26 19:09:00
2   3  2022-07-27 14:13:00  2022-07-27 20:13:00
3   4  2022-07-28 15:10:00  2022-07-28 21:22:00

我已经能通过以下语句跳过first_meal列的第一行：

select f.id, f.first_meal from lifelong f offset 1;

也能通过以下语句查询全部last_meal列数据：

select id, last_meal from lifelong;

现在需要把这两个查询的结果合并成一张新表，期望输出如下：

id           first_meal            last_meal
0   1  2022-07-26 13:12:00  2022-07-25 20:06:00
1   2  2022-07-27 14:13:00  2022-07-26 19:09:00
2   3  2022-07-28 15:10:00  2022-07-27 20:13:00
3   4                  NaN  2022-07-28 21:22:00

之前试了UNION、UNION ALL或者子查询嵌套，都没得到想要的结果，求正确的SQL写法。

你要的是列对齐合并，而UNION系列是用来合并行记录的，完全不适合这个场景。这里给两种可行的写法：

方法1：用窗口函数LEAD()（推荐） #

直接用窗口函数获取下一行的first_meal，和原表的last_meal对应即可：

SELECT
    id,
    LEAD(first_meal) OVER (ORDER BY id) AS first_meal,
    last_meal
FROM lifelong;

• LEAD(first_meal) OVER (ORDER BY id)：按id升序排列，自动取当前行的下一行first_meal值，最后一行没有下一行，就返回NULL（对应你要的NaN，SQL里用NULL表示空值）。
• 这条语句直接从原表取数，一步生成你要的结果。

方法2：自连接（兼容老版本数据库） #

如果你的数据库不支持窗口函数，用自连接也能实现：

SELECT
    l1.id,
    l2.first_meal,
    l1.last_meal
FROM lifelong l1
LEFT JOIN lifelong l2 ON l1.id + 1 = l2.id;

• 把表自己和自己连接，让l1的id加1等于l2的id，这样l1的每一行就能对应l2的下一行，拿到偏移后的first_meal。
• LEFT JOIN保证最后一行（id=4）没有匹配的l2行，first_meal会返回NULL。

保存为新表 #

要把结果存成新表，在上述语句前加CREATE TABLE new_table AS（不同数据库语法略有区别，比如SQL Server用SELECT ... INTO new_table FROM ...）：
以MySQL/PostgreSQL为例：

CREATE TABLE new_table AS
SELECT
    id,
    LEAD(first_meal) OVER (ORDER BY id) AS first_meal,
    last_meal
FROM lifelong;

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