PostgreSQL-04-入门篇-连接多张表

1. 连接

设置样例表

假设您有两个名为basket_a和basket_b的表，用于存储水果信息：

CREATE TABLE basket_a (
    a INT PRIMARY KEY,
    fruit_a VARCHAR (100) NOT NULL
);

CREATE TABLE basket_b (
    b INT PRIMARY KEY,
    fruit_b VARCHAR (100) NOT NULL
);

INSERT INTO basket_a (a, fruit_a)
VALUES
    (1, 'Apple'),
    (2, 'Orange'),
    (3, 'Banana'),
    (4, 'Cucumber');

INSERT INTO basket_b (b, fruit_b)
VALUES
    (1, 'Orange'),
    (2, 'Apple'),
    (3, 'Watermelon'),
    (4, 'Pear');

桌子上有一些常见的水果，例如apple和orange。

以下语句通过匹配fruit_a和fruit_b列中的值来连接第一个表 (basket_a) 和第二个表 (basket_b) ：

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
INNER JOIN basket_b
    ON fruit_a = fruit_b;

内连接检查第一个表 (basket_a) 中的每一行。它将fruit_a列中的值与第二个表 (basket_b) 中每行的fruit_b列中的值进行比较。如果这些值相等，则内部联接将创建一个包含两个表中的列的新行，并将新行添加到结果集中。

下面的维恩图说明了内连接：

PostgreSQL 左连接

以下语句使用左连接子句将basket_a表与basket_b表连接起来。在左连接上下文中，第一个表称为左表，第二个表称为右表。

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
LEFT JOIN basket_b 
   ON fruit_a = fruit_b;

左连接开始从左表中查询数据。它将fruit_a列中的值与basket_b表中fruit_b列中的值进行比较。

如果这些值相等，则左联接将创建一个包含两个表的列的新行，并将新行添加到结果集中。（请参阅结果集中的第 1 行和第 2 行）。

如果值不相等，左连接还会创建一个新行，其中包含两个表中的列，并将其添加到结果集中。但是，它用NULL填充右表 (basket_b) 的列。（请参阅结果集中的第 3 行和第 4 行）。

下面的维恩图说明了左连接：

要从左表中查询右表中没有匹配行的行，请使用带WHERE子句的左连接。例如：

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
LEFT JOIN basket_b 
    ON fruit_a = fruit_b
WHERE b IS NULL;

输出是：

请注意， LEFT JOIN与LEFT OUTER JOIN相同，因此您可以互换使用它们。

下面的维恩图说明了左连接，它返回左表中与右表中没有匹配行的行：

PostgreSQL 右连接

右连接是左连接的反转版本。右连接开始从右表中选择数据。它将右表中每行的fruit_b列中的每个值与basket_a表中每行的fruit_a列中的每个值进行比较。

如果这些值相等，则右连接将创建一个新行，其中包含两个表中的列。

如果这些值不相等，右连接还会创建一个新行，其中包含两个表中的列。但是，它用NULL填充左表中的列。

以下语句使用右连接将basket_a表与basket_b表连接起来：

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
RIGHT JOIN basket_b ON fruit_a = fruit_b;

这是输出：

下面的维恩图说明了右连接：

同样，您可以通过添加WHERE子句从右表中获取与左表中没有匹配行的行，如下所示：

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
RIGHT JOIN basket_b 
   ON fruit_a = fruit_b
WHERE a IS NULL;

RIGHT JOIN和RIGHT OUTER JOIN是相同的，因此您可以互换使用它们。

下面的维恩图说明了右连接，它返回右表中的行，这些行在左表中没有匹配的行：

PostgreSQL 全外连接

完全外连接或完全连接返回一个结果集，其中包含左表和右表中的所有行，以及两侧的匹配行（如果有）。如果没有匹配，表的列将被填充为NULL。

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
FULL OUTER JOIN basket_b 
    ON fruit_a = fruit_b;

输出：

下面的维恩图说明了完全外连接：

要返回一个表中的行，而另一个表中没有匹配的行，可以使用带有WHERE子句的完全连接，如下：

SELECT
    a,
    fruit_a,
    b,
    fruit_b
FROM
    basket_a
FULL JOIN basket_b 
   ON fruit_a = fruit_b
WHERE a IS NULL OR b IS NULL;

结果如下：

下面的维恩图说明了完全外连接，该连接返回一个表中的行，而另一个表中没有对应的行：

下图显示了我们到目前为止讨论的所有 PostgreSQL 连接的详细语法：

2. 表别名

简介

表别名在执行查询期间临时为表分配新名称。

下面说明了表别名的语法：

table_name AS alias_name;

在此语法中，为 table_name 分配了一个别名 alias_name。与列别名类似，AS 关键字是可选的。这意味着您可以像这样省略 AS 关键字：

表别名的实际应用

表别名有多种实际应用。

1) 对长表名使用表别名，使查询更具可读性

如果必须使用长表名限定列名，则可以使用表别名来节省一些输入并使查询更具可读性。

例如，当您在查询中使用了以下表达式：

a_very_long_table_name.column_name

您可以为表a_very_long_table_name分配一个别名，如下所示：

a_very_long_table_name AS alias

并使用表别名引用表a_very_long_table_name中的column_name：

2) 在连接子句中使用表别名

通常，您经常会使用 join 子句从具有相同列名的多个表中查询数据。
如果您使用来自多个表的相同列名而没有完全限定它们，您将收到错误。
为了避免此错误，您需要使用以下语法限定这些列：
为了使查询更短，您可以使用 FROM 和INNER JOIN子句中列出的表名的表别名。例如：

SELECT
c.customer_id,
first_name,
amount,
payment_date
FROM
customer c
INNER JOIN payment p 
    ON p.customer_id = c.customer_id
ORDER BY 
   payment_date DESC;

3) 在自连接中使用表别名

当您将表连接到自身（也称为自连接）时，您需要使用表别名。这是因为在查询中多次引用同一个表会导致错误。

以下示例演示如何使用表别名在同一查询中两次引用employee表：

SELECT
    e.first_name employee,
    m .first_name manager
FROM
    employee e
INNER JOIN employee m 
    ON m.employee_id = e.manager_id
ORDER BY manager;

3. INNER JOIN 内连接

简介

在关系数据库中，数据通常分布在多个表中。为了查询完整的数据，经常需要从多个表中查询数据。

假设有两个表 A 和 B。表 A 有一个列pka，其值与表 B 的fka列中的值匹配。

要从两个表中查询数据，请在SELECT语句中使用INNER JOIN子句，如下所示：

SELECT
pka,
c1,
pkb,
c2
FROM
A
INNER JOIN B ON pka = fka;

要将表A与表B连接，请按照下列步骤操作：

• 首先，指定要在SELECT列表子句中查询数据的两个表中的列。
• 其次，在FROM子句中指定主表，即表A。
• 第三，在INNER JOIN子句中指定第二个表 (B) ，并在ON关键字后提供连接条件。

INNER JOIN是如何运作的？

对于表A中的每一行，内连接将pka列中的值与表B中每一行的fka列中的值进行比较：

• 如果这些值相等，则内部联接将创建一个包含两个表的所有列的新行，并将其添加到结果集中。
• 如果这些值不相等，内连接将忽略它们并移至下一行。

下面的维恩图说明了INNER JOIN子句的工作原理。

大多数时候，您想要连接的表将具有相同名称的列，例如，像customer_id这样的id列。

如果在查询中引用不同表中具有相同名称的列，则会出现错误。为了避免该错误，您需要使用以下语法完全限定这些列：

table_name.column_name

PostgreSQL INNER JOIN 示例

让我们看一些使用INNER JOIN子句的例子。

1) 使用 INNER JOIN 连接两个表

我们来看看示例数据库中的customer和payment表。

在这些表中，每当客户付款时，就会在payment表中插入一个新行。

每个客户可能有零次或多次付款。但是，每笔付款只属于一位客户。customer_id列建立了两个表之间的关系。

以下语句使用INNER JOIN子句从两个表中查询数据：

SELECT
customer.customer_id,
first_name,
last_name,
amount,
payment_date
FROM
customer
INNER JOIN payment 
    ON payment.customer_id = customer.customer_id
ORDER BY payment_date;

以下查询返回相同的结果。但是，它使用了表别名：

SELECT
c.customer_id,
first_name,
last_name,
email,
amount,
payment_date
FROM
customer c
INNER JOIN payment p 
    ON p.customer_id = c.customer_id
WHERE
    c.customer_id = 2;

由于两个表具有相同的customer_id列，因此您可以使用USING语法，如下：

SELECT
customer_id,
first_name,
last_name,
amount,
payment_date
FROM
customer
INNER JOIN payment USING(customer_id)
ORDER BY payment_date;

2) 使用 INNER JOIN 连接三个表

下图说明了三个表之间的关系：staff、payment和customer。

• 每个员工处理零笔或多笔付款。每笔付款均由一名且只有一名工作人员处理。
• 每个客户进行零次或多次付款。每笔付款均由一位客户进行。

要连接三个表，请将第二个INNER JOIN子句放置在第一个INNER JOIN子句之后，查询如下：

SELECT
c.customer_id,
c.first_name customer_first_name,
c.last_name customer_last_name,
s.first_name staff_first_name,
s.last_name staff_last_name,
amount,
payment_date
FROM
customer c
INNER JOIN payment p 
    ON p.customer_id = c.customer_id
INNER JOIN staff s 
    ON p.staff_id = s.staff_id
ORDER BY payment_date;

要连接三个以上的表，可以应用相同的技术。

4. LEFT JOIN 左连接

简介

假设您有两个表：A和B。

表A中的每一行在表B中可能有零个或多个对应的行，而表B中的每一行在表A中只有一个对应的行。

要从表A中查询在表B中可能有也可能没有对应行的数据，可以使用LEFT JOIN子句。

以下语句说明了连接表A与表B的LEFT JOIN语法：

SELECT
pka,
c1,
pkb,
c2
FROM
A
LEFT JOIN B ON pka = fka;

要使用左连接将表A与表B连接起来，请执行以下步骤：

• 首先，指定两个表中要在SELECT列表子句中查询数据的列。
• 其次，在FROM子句中指定左表（表A）。
• 第三，在LEFT JOIN子句中指定右表（表B），并在ON关键字后指定连接条件。

LEFT JOIN子句开始从左表中查询数据。对于左表中的每一行，它将pka列中的值与右表中每行中的fka列值进行比较。

如果这些值相等，则左连接子句将创建一个新行，其中包含SELECT列表子句中出现的列，并将该行添加到结果集中。

如果这些值不相等，左连接子句还会创建一个新行，其中包含SELECT列表子句中出现的列。此外，它还用 NULL 填充来自右表的列。

下面的维恩图说明了LEFT JOIN子句的工作原理：

请注意，LEFT JOIN也称为LEFT OUTER JOIN。

PostgreSQL LEFT JOIN 示例

film表中的每一行在inventory表中可能有零行或多行。inventory表中的每一行在film表中有且仅有一行。

film_id列建立了film和inventory表之间的链接。

以下语句使用LEFT JOIN子句将film表与inventory表连接起来：

SELECT
film.film_id,
title,
inventory_id
FROM
film
LEFT JOIN inventory 
    ON inventory.film_id = film.film_id
ORDER BY title;

当film表中的某行在inventory表中没有匹配行时，该行的inventory_id列值为NULL。

以下语句添加了一个WHERE子句来查找不在inventory表中的影片：

SELECT
film.film_id,
film.title,
inventory_id
FROM
film
LEFT JOIN inventory 
   ON inventory.film_id = film.film_id
WHERE inventory.film_id IS NULL
ORDER BY title;

如果两个表在ON子句中使用相同的列名，则可以使用USING语法，如下：

SELECT
f.film_id,
title,
inventory_id
FROM
film f
LEFT JOIN inventory i USING (film_id)
WHERE i.film_id IS NULL
ORDER BY title;

5. 自连接

简介

自连接是一种将表与其自身连接的常规连接。在实践中，您通常使用自连接来查询分层数据或比较同一表中的行。

要形成自连接，请使用不同的表别名指定同一表两次，并在ON关键字后提供连接谓词。

以下查询使用INNER JOIN将表连接到自身：

SELECT select_list
FROM table_name t1
INNER JOIN table_name t2 ON join_predicate;

在此语法中，使用INNER JOIN子句将table_name与其自身连接起来。

另外，您可以使用LEFT JOIN或RIGHT JOIN子句将表连接到自身，如下所示：

SELECT select_list
FROM table_name t1
LEFT JOIN table_name t2 ON join_predicate;

PostgreSQL 自连接示例

让我们举一些使用自连接的例子。

1) 查询分层数据示例

让我们设置一个示例表来进行演示。

假设，您有以下组织结构：

以下语句创建employee表并向表中插入一些示例数据。

CREATE TABLE employee (
employee_id INT PRIMARY KEY,
first_name VARCHAR (255) NOT NULL,
last_name VARCHAR (255) NOT NULL,
manager_id INT,
FOREIGN KEY (manager_id) 
REFERENCES employee (employee_id) 
ON DELETE CASCADE
);
INSERT INTO employee (
employee_id,
first_name,
last_name,
manager_id
)
VALUES
(1, 'Windy', 'Hays', NULL),
(2, 'Ava', 'Christensen', 1),
(3, 'Hassan', 'Conner', 1),
(4, 'Anna', 'Reeves', 2),
(5, 'Sau', 'Norman', 2),
(6, 'Kelsie', 'Hays', 3),
(7, 'Tory', 'Goff', 3),
(8, 'Salley', 'Lester', 3);

在此employee表中，manager_id列引用employee_id列。manager_id列中的值显示员工直接向其汇报的经理。当manager_id列中的值为空时，该员工不向任何人报告。换句话说，他或她是最高管理者。

以下查询使用自连接来查找谁向谁报告：

SELECT
    e.first_name || ' ' || e.last_name employee,
    m .first_name || ' ' || m .last_name manager
FROM
    employee e
INNER JOIN employee m ON m .employee_id = e.manager_id
ORDER BY manager;

此查询两次引用employees表，一次作为员工表，另一次作为经理表。它使用表别名e标识员工表和表别名m标识经理表。

连接谓词通过匹配employee_id和manager_id列中的值来查找员工/经理对。

请注意，最高管理者不会出现在输出中。

要将最高管理者包含在结果集中，请使用LEFT JOIN替代INNER JOIN子句，如以下查询所示：

SELECT
    e.first_name || ' ' || e.last_name employee,
    m .first_name || ' ' || m .last_name manager
FROM
    employee e
LEFT JOIN employee m ON m .employee_id = e.manager_id
ORDER BY manager;

2) 比较同一个表的行

请查看 DVD 租赁数据库中的film表，如下：

以下查询查找所有具有相同长度的电影对，

SELECT
    f1.title,
    f2.title,
    f1.length
FROM
    film f1
INNER JOIN film f2 
    ON f1.film_id <> f2.film_id AND 
       f1.length = f2.length;

连接谓词匹配具有相同长度 (f1.length = f2.length) 的两个不同电影 (f1.film_id <> f2.film_id)。

概括

• PostgreSQL 自连接是一种常规连接，它使用INNER JOIN或LEFT JOIN将表与其自身连接。

• 自连接对于查询分层数据或比较同一表中的行非常有用。

6. 使用FULL OUTER JOIN 进行全外连接

简介

假设您要执行表 A 和表 B 的完全外连接。以下说明了FULL OUTER JOIN语法：

SELECT * FROM A
FULL [OUTER] JOIN B on A.id = B.id;

在此语法中，OUTER关键字是可选的。

完全外连接结合了左连接和右连接的结果。

如果连接表中的行不匹配，则完全外连接会为表中没有匹配行的每一列设置 NULL 值。

如果一个表中的一行与另一个表中的行匹配，则结果行将包含由两个表中的行列填充的列。

下面的维恩图说明了FULL OUTER JOIN操作：

结果包括两个表中的匹配行以及不匹配的行。

PostgreSQL FULL OUTER JOIN 示例

首先，创建两个新表用于演示：employees和departments：

DROP TABLE IF EXISTS departments;
DROP TABLE IF EXISTS employees;

CREATE TABLE departments (
department_id serial PRIMARY KEY,
department_name VARCHAR (255) NOT NULL
);

CREATE TABLE employees (
employee_id serial PRIMARY KEY,
employee_name VARCHAR (255),
department_id INTEGER
);

每个部门有零个或多个员工，每个员工属于零个或一个部门。

其次，将一些示例数据插入到departments和employees表中。

INSERT INTO departments (department_name)
VALUES
('Sales'),
('Marketing'),
('HR'),
('IT'),
('Production');

INSERT INTO employees (
employee_name,
department_id
)
VALUES
('Bette Nicholson', 1),
('Christian Gable', 1),
('Joe Swank', 2),
('Fred Costner', 3),
('Sandra Kilmer', 4),
('Julia Mcqueen', NULL);

第三步，使用FULL OUTER JOIN从employees和departments表中查询数据。

SELECT
employee_name,
department_name
FROM
employees e
FULL OUTER JOIN departments d 
        ON d.department_id = e.department_id;

结果集包括属于某个部门的每个员工以及拥有该员工的每个部门。此外，它还包括不属于某个部门的每个员工以及没有员工的每个部门。

要查找没有任何员工的部门，请使用 WHERE子句，如下所示：

SELECT
employee_name,
department_name
FROM
employees e
FULL OUTER JOIN departments d 
        ON d.department_id = e.department_id
WHERE
employee_name IS NULL;

结果显示Production部门没有任何员工。

要查找不属于任何部门的员工，请检查WHERE子句中的department_name列值为NULL，如下所示：

SELECT
employee_name,
department_name
FROM
employees e
FULL OUTER JOIN departments d ON d.department_id = e.department_id
WHERE
department_name IS NULL;

7. 使用CROSS JOIN 进行交叉连接

简介

一个CROSS JOIN子句允许您生成两个或多个表中的行的笛卡尔积。

与其他连接子句（例如LEFT JOIN 或INNER JOIN）不同，CROSS JOIN子句没有连接谓词。

假设您必须使用CROSS JOIN连接两个表 T1 和 T2。

如果 T1 有n行且 T2 有m行，则结果集将有n * m行。例如，T1 有1,000行，T2 有1,000行，结果集将有1,000 x 1,000=1,000,000行。

下面举例说明CROSS JOIN语法的语法结构：

SELECT select_list
FROM T1
CROSS JOIN T2;

下面的语句与上面的语句是等价的：

SELECT select_list
FROM T1, T2;

此外，您可以使用一个条件始终为true的INNER JOIN子句来模拟交叉连接：

SELECT *
FROM T1
INNER JOIN T2 ON true;

PostgreSQL CROSS JOIN 示例

以下 CREATE TABLE 语句创建 T1 和 T2 表，并插入一些示例数据以进行演示。

DROP TABLE IF EXISTS T1;
CREATE TABLE T1 (label CHAR(1) PRIMARY KEY);

DROP TABLE IF EXISTS T2;
CREATE TABLE T2 (score INT PRIMARY KEY);

INSERT INTO T1 (label)
VALUES
('A'),
('B');

INSERT INTO T2 (score)
VALUES
(1),
(2),
(3);

以下语句使用CROSS JOIN运算符连接表 T1 和表 T2。

SELECT *
FROM T1
CROSS JOIN T2;

 label | score
-------+-------
 A     |     1
 B     |     1
 A     |     2
 B     |     2
 A     |     3
 B     |     3
(6 rows)

下图展示了使用CROSS JOIN将表 T1 连接到表 T2 时的结果：

8. 使用NATURAL JOIN 进行自然连接

简介

自然连接是一种基于连接表中相同列名创建隐式连接的连接。

下面显示了 PostgreSQL 自然连接的语法：

SELECT select_list
FROM T1
NATURAL [INNER, LEFT, RIGHT] JOIN T2;

自然连接可以是内连接、左连接或右连接。如果您没有显式指定连接，例如INNER JOIN, LEFT JOIN, RIGHT JOIN，PostgreSQL 将默认使用INNER JOIN。

如果在选择列表中使用星号 (*)，结果将包含以下列：

• 所有公共列，即两个表中具有相同名称的列。
• 两个表中的每一列，这不是公共列。

PostgreSQL NATURAL JOIN 示例

为了演示 PostgreSQL 自然连接，我们将创建两个表：categories和products。

以下CREATE TABLE语句创建categories和products表。

DROP TABLE IF EXISTS categories;
CREATE TABLE categories (
category_id serial PRIMARY KEY,
category_name VARCHAR (255) NOT NULL
);

DROP TABLE IF EXISTS products;
CREATE TABLE products (
product_id serial PRIMARY KEY,
product_name VARCHAR (255) NOT NULL,
category_id INT NOT NULL,
FOREIGN KEY (category_id) REFERENCES categories (category_id)
);

每个类别有零个或多个产品，每个产品属于一个且仅一个类别。

products表中的category_id列是引用categories表主键的外键。category_id是我们将用来执行自然连接的公共列。

以下 INSERT语句将一些数据插入到categories和products表中。

INSERT INTO categories (category_name)
VALUES
('Smart Phone'),
('Laptop'),
('Tablet');

INSERT INTO products (product_name, category_id)
VALUES
('iPhone', 1),
('Samsung Galaxy', 1),
('HP Elite', 2),
('Lenovo Thinkpad', 2),
('iPad', 3),
('Kindle Fire', 3);

以下语句使用NATURAL JOIN子句将products表与categories表连接起来：

SELECT * FROM products
NATURAL JOIN categories;

上面的语句等价于下面使用INNER JOIN子句的语句。

SELECT* FROM products
INNER JOIN categories USING (category_id);

NATURAL JOIN的便利之处在于它不需要您指定 join 子句，因为它使用基于公共列的隐式 join 子句。

但是，您应该尽可能避免使用NATURAL JOIN，因为有时它可能会导致意外结果。

例如，有如下两个表：

两个表具有相同的country_id列，因此您可以使用NATURAL JOIN来连接这些表，如下所示：

SELECT * 
FROM city
NATURAL JOIN country;

该查询返回一个空结果集。

原因是……

两个表还有另一个称为last_update的公共列，该列不能用于连接。但是，该NATURAL JOIN子句还是使用了last_update列。

9. 使用 LATERAL JOIN 进行横向连接

数据表

假设我们有一个 blog 表，用于存储由我们的平台托管的博客信息：

id	created_on	title	url
1	2024-09-30	Blog 1	https://xxx/blog1/
2	2024-01-22	Blog 2	https://xxx/blog2/

我们需要生成一个报表，用于从 blog 表中提取以下数据：

• 博客 ID
• 博客年龄（以年为单位）
• 下一次博客周年纪念日的日期
• 距离下一次周年纪念日还剩多少天

计算博客年龄

博客年龄通过将当前日期减去博客创建日期来计算。

下一次博客周年纪念日的日期可以通过将年龄加一并将其添加到博客创建日期来计算。

通过从下一次周年纪念日和当前日期之间的间隔中提取天数，可以计算到下一次周年纪念日的天数。

若要获取结果，可以使用以下查询：

 SELECT
  b.id AS blog_id,
  EXTRACT(YEAR FROM AGE(NOW(), b.created_on)) AS age_in_years,
  DATE(b.created_on + (EXTRACT(YEAR FROM AGE(NOW(), b.created_on)) + 1) ```sql INTERVAL '1 YEAR') AS next_anniversary,
  DATE(b.created_on + (EXTRACT(YEAR FROM AGE(NOW(), b.created_on)) + 1) ```sql INTERVAL '1 YEAR') - DATE(NOW()) AS days_to_next_anniversary
 FROM blog b
 ORDER BY blog_id;

这样，您将获得预期的结果：

blog_id	age_in_years	next_anniversary	days_to_next_anniversary
1	7	2021-09-30	295
2	3	2021-01-22	44

如您所见，age_in_years 必须定义三次，因为在计算 next_anniversary 和 days_to_next_anniversary 值时需要它。

而这就是 LATERAL JOIN 可以帮助我们的地方。

使用 LATERAL JOIN 获取报表

LATERAL JOIN 允许我们只计算一次 age_in_years 值，并在计算 next_anniversary 和 days_to_next_anniversary 值时重用它。

例如，前面的 SQL 查询可以重写为：

 SELECT
  b.id AS blog_id,
  age_in_years,
  DATE(b.created_on + (age_in_years + 1) ```sql INTERVAL '1 YEAR') AS next_anniversary,
  DATE(b.created_on + (age_in_years + 1) ```sql INTERVAL '1 YEAR') - DATE(NOW()) AS days_to_next_anniversary
 FROM blog b
 CROSS JOIN LATERAL (
  SELECT
    CAST(EXTRACT(YEAR FROM AGE(NOW(), b.created_on)) AS INT) AS age_in_years
 ) AS t
 ORDER BY blog_id;

这样，age_in_years 值只需计算一次，并且可以在计算 next_anniversary 和 days_to_next_anniversary 时重用。

最终结果如下：

blog_id	age_in_years	next_anniversary	days_to_next_anniversary
1	7	2021-09-30	295
2	3	2021-01-22	44

LATERAL JOIN 的工作方式类似于相关的子查询，但它使得子查询的结果能够被主查询中的其他部分引用。