PostgreSQL-04-入门篇-连接多张表
文章目录
- 1. 连接
- 设置样例表
- PostgreSQL 左连接
- PostgreSQL 右连接
- PostgreSQL 全外连接
- 2. 表别名
- 简介
- 表别名的实际应用
- 1) 对长表名使用表别名,使查询更具可读性
- 2) 在连接子句中使用表别名
- 3) 在自连接中使用表别名
- 3. INNER JOIN 内连接
- 简介
- PostgreSQL INNER JOIN 示例
- 1) 使用 INNER JOIN 连接两个表
- 2) 使用 INNER JOIN 连接三个表
- 4. LEFT JOIN 左连接
- 简介
- PostgreSQL LEFT JOIN 示例
- 5. 自连接
- 简介
- PostgreSQL 自连接示例
- 1) 查询分层数据示例
- 2) 比较同一个表的行
- 概括
- 6. 使用FULL OUTER JOIN 进行全外连接
- 简介
- PostgreSQL FULL OUTER JOIN 示例
- 7. 使用CROSS JOIN 进行交叉连接
- 简介
- PostgreSQL CROSS JOIN 示例
- 8. 使用NATURAL JOIN 进行自然连接
- 简介
- PostgreSQL NATURAL JOIN 示例
- 9. 使用 LATERAL JOIN 进行横向连接
- 数据表
- 计算博客年龄
- 使用 LATERAL JOIN 获取报表
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
) :
SELECTa,fruit_a,b,fruit_b
FROMbasket_a
INNER JOIN basket_bON fruit_a = fruit_b;
内连接检查第一个表 (basket_a
) 中的每一行。它将fruit_a
列中的值与第二个表 (basket_b
) 中每行的fruit_b
列中的值进行比较。如果这些值相等,则内部联接将创建一个包含两个表中的列的新行,并将新行添加到结果集中。
下面的维恩图说明了内连接:
PostgreSQL 左连接
以下语句使用左连接子句将basket_a
表与basket_b
表连接起来。在左连接上下文中,第一个表称为左表,第二个表称为右表。
SELECTa,fruit_a,b,fruit_b
FROMbasket_a
LEFT JOIN basket_b ON fruit_a = fruit_b;
左连接开始从左表中查询数据。它将fruit_a
列中的值与basket_b
表中fruit_b
列中的值进行比较。
如果这些值相等,则左联接将创建一个包含两个表的列的新行,并将新行添加到结果集中。(请参阅结果集中的第 1 行和第 2 行)。
如果值不相等,左连接还会创建一个新行,其中包含两个表中的列,并将其添加到结果集中。但是,它用NULL
填充右表 (basket_b
) 的列。(请参阅结果集中的第 3 行和第 4 行)。
下面的维恩图说明了左连接:
要从左表中查询右表中没有匹配行的行,请使用带WHERE
子句的左连接。例如:
SELECTa,fruit_a,b,fruit_b
FROMbasket_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
表连接起来:
SELECTa,fruit_a,b,fruit_b
FROMbasket_a
RIGHT JOIN basket_b ON fruit_a = fruit_b;
这是输出:
下面的维恩图说明了右连接:
同样,您可以通过添加WHERE
子句从右表中获取与左表中没有匹配行的行,如下所示:
SELECTa,fruit_a,b,fruit_b
FROMbasket_a
RIGHT JOIN basket_b ON fruit_a = fruit_b
WHERE a IS NULL;
RIGHT JOIN
和RIGHT OUTER JOIN
是相同的,因此您可以互换使用它们。
下面的维恩图说明了右连接,它返回右表中的行,这些行在左表中没有匹配的行:
PostgreSQL 全外连接
完全外连接或完全连接返回一个结果集,其中包含左表和右表中的所有行,以及两侧的匹配行(如果有)。如果没有匹配,表的列将被填充为NULL
。
SELECTa,fruit_a,b,fruit_b
FROMbasket_a
FULL OUTER JOIN basket_b ON fruit_a = fruit_b;
输出:
下面的维恩图说明了完全外连接:
要返回一个表中的行,而另一个表中没有匹配的行,可以使用带有WHERE
子句的完全连接,如下:
SELECTa,fruit_a,b,fruit_b
FROMbasket_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
表:
SELECTe.first_name employee,m .first_name manager
FROMemployee 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
WHEREc.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
列中的值为空时,该员工不向任何人报告。换句话说,他或她是最高管理者。
以下查询使用自连接来查找谁向谁报告:
SELECTe.first_name || ' ' || e.last_name employee,m .first_name || ' ' || m .last_name manager
FROMemployee 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
子句,如以下查询所示:
SELECTe.first_name || ' ' || e.last_name employee,m .first_name || ' ' || m .last_name manager
FROMemployee e
LEFT JOIN employee m ON m .employee_id = e.manager_id
ORDER BY manager;
2) 比较同一个表的行
请查看 DVD 租赁数据库中的film
表,如下:
以下查询查找所有具有相同长度的电影对,
SELECTf1.title,f2.title,f1.length
FROMfilm 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 | 1B | 1A | 2B | 2A | 3B | 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
- 博客年龄(以年为单位)
- 下一次博客周年纪念日的日期
- 距离下一次周年纪念日还剩多少天
计算博客年龄
博客年龄通过将当前日期减去博客创建日期来计算。
下一次博客周年纪念日的日期可以通过将年龄加一并将其添加到博客创建日期来计算。
通过从下一次周年纪念日和当前日期之间的间隔中提取天数,可以计算到下一次周年纪念日的天数。
若要获取结果,可以使用以下查询:
SELECTb.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_anniversaryFROM blog bORDER 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 查询可以重写为:
SELECTb.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_anniversaryFROM blog bCROSS JOIN LATERAL (SELECTCAST(EXTRACT(YEAR FROM AGE(NOW(), b.created_on)) AS INT) AS age_in_years) AS tORDER 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 的工作方式类似于相关的子查询,但它使得子查询的结果能够被主查询中的其他部分引用。