PostgreSQL 中 LATERAL JOIN 横向关联

sql SELECT ... FROM left_table JOIN LATERAL ( subquery_or_function ) AS alias ON true

- `LATERAL` 只能出现在 `FROM` 子句的连接中，可以用于 `INNER JOIN`、`LEFT JOIN` 等。
- 右侧的子查询或函数可以引用左侧表的任何列。
- 对于内连接，可以省略 `ON true`（但显式写出更清晰）；对于左连接，必须保留 `ON` 条件或 `ON true`。

另一种常见写法是直接使用逗号，这在 PostgreSQL 中等价于 `CROSS JOIN LATERAL`：

```sql
SELECT *
FROM left_table,
LATERAL some_function(left_table.column) AS alias;

示例一：与集返回函数结合

假设有一个表 projects，每条记录代表一个项目，我们希望为每个项目生成从 1 到 tasks_count 的任务编号。

SELECT p.project_name, task_no
FROM projects p
CROSS JOIN LATERAL generate_series(1, p.tasks_count) AS task_no;

这里 generate_series 的第二个参数引用了 p.tasks_count，如果没有 LATERAL，这句会报错。该查询为每个项目产生与其任务数相等的行。

示例二：查找每个分类下最新的 N 条记录

有一个 products 表，每个产品属于一个分类，我们想取出每个分类中价格最高的 3 个产品。

SELECT c.category_name, best.name, best.price
FROM categories c
LEFT JOIN LATERAL (
    SELECT p.name, p.price
    FROM products p
    WHERE p.category_id = c.id
    ORDER BY p.price DESC
    LIMIT 3
) best ON true;

对于每个分类 c，右侧子查询都会使用 c.id 作为过滤条件，取出其价格最高的 3 个产品。使用 LEFT JOIN 可以保留没有产品的分类。

示例三：在 SELECT 中多次引用计算列

有时我们需要基于一个复杂的计算结果做过滤，同时又想在结果中保留该值。LATERAL 可以让计算只发生一次。

SELECT u.username, calc.total_spent
FROM users u,
LATERAL (
    SELECT COALESCE(SUM(amount), 0) AS total_spent
    FROM orders
    WHERE orders.user_id = u.id
) calc
WHERE calc.total_spent > 1000;

这里 calc.total_spent 不仅出现在 SELECT 中，还用于 WHERE 过滤，而聚合计算只在 LATERAL 子查询中执行一次。

LATERAL 与 LEFT JOIN 的配合

LEFT JOIN LATERAL 的行为和普通左连接一致：当右侧子查询没有返回任何行时，右侧列填充 NULL。

这对于“可能没有结果”的场景非常有用，例如为每篇文章获取最新的一条评论，即使文章没有评论也显示一行：

SELECT a.title, latest.comment_body, latest.created_at
FROM articles a
LEFT JOIN LATERAL (
    SELECT comment_body, created_at
    FROM comments
    WHERE article_id = a.id
    ORDER BY created_at DESC
    LIMIT 1
) latest ON true;