PostgreSQL连接另一个表的第一个结果

Question

我有以下表格:

文章:

| id (pk) | title   |
|---------|---------|
| 1       | title 1 |
| 2       | title 2 |

图片:

| id (pk) | post_id (fk) | display_order | image_path |
|---------|--------------|---------------|------------|
| 1       | 1            | 1             | image1.jpg |
| 2       | 1            | 2             | image2.jpg |
| 3       | 1            | 0             | image3.jpg |
| 4       | 2            | 1             | image4.jpg |
| 5       | 2            | 1             | image5.jpg |
| 6       | 2            | 2             | image6.jpg |

我想检索一个帖子列表，每个帖子包含基于display_order的图像中的第一个项目。

期望的结果是这样的:

| id | title   | thumb_path |
|----|---------|------------|
| 1  | title 1 | image3.jpg |
| 2  | title 2 | image4.jpg |

我该怎么做?

Answer 1

一个经典的PostgreSQL解决方案是distinct on子句

select distinct on (post_id) post_id, image_path
from images 
order by post_id, display_order, image_path

每个post_id的第一行用order by

定义剩下的是一个简单的join

with img as (
select distinct on (post_id) post_id, image_path
from images 
order by post_id, display_order, image_path
)
select p.id, p.title, img.image_path
from posts p
join img on p.id = img.post_id

Answer 2

使用横向连接只能获得相关表上的一行。例如:

select p.*, x.image_path
from posts p
left join lateral (
select *
from images i where i.post_id = p.id
order by display_order
limit 1
) x on true