我有两个书籍和作者表,如下所示,
图书表:
id | name | author_id |
----------------------------
1 | Java | 1 |
1 | Spring | 1 |
1 | JSF | 1 |
1 | Apache | 1 |
1 | Scala | 1 |
1 | PHP | 2 |
1 | Laravel | 2 |
1 | Node | 3 |
1 | Vue | 3 |
作者表:
id | name |
-----------------------
1 | Gulsan Singh |
2 | Chandan Singh |
3 | Charan Putrevu |
我想搜索姓名和作者字段,所以我使用了以下查询,
SELECT a.name AS authorName, b.name AS bookName FROM author a LEFT JOIN books b on a.id = b.author_id WHERE
a.name LIKE '%Singh%' OR b.name LIKE '%Singh%' LIMIT 5
但此查询按以下方式返回结果
authorName | bookName |
----------------------------
Gulsan Singh | Java |
Gulsan Singh | Spring |
Gulsan Singh | JSF |
Gulsan Singh | Apache |
Gulsan Singh | Scala |
正因为如此,我错过了第二作者Chandan Singh
因为我有LIMIT 5
.我想避免这种重复,但如果搜索查询与任何书籍名称匹配,那么作者可能会显示多次。但是当 bookName 列没有匹配项但在 authorName 列中有一个匹配项时,这里会显示几次作者。
希望我在这里很清楚,是否有可能实现我的目标?
您可以在limit
之前对数据重新排序:
SELECT a.name AS authorName, b.name AS bookName
FROM author a LEFT JOIN
books b
ON a.id = b.author_id
WHERE a.name LIKE '%Singh%' OR b.name LIKE '%Singh%'
ORDER BY ROW_NUMBER() OVER (PARTITION BY COALESCE(a.name, b.name))
LIMIT 5;
Postgres不需要ORDER BY
ROW_NUMBER()
。 如果您对订购有偏好,可以包括一个。