如何为PostgreSQL 15.2的SELECT语句添加指定字段排序？

你当前的查询可正常运行，要按照siteId、pageId、clusterId、frequency四个字段排序，只需在整个SQL语句的末尾追加ORDER BY子句即可，这四个字段都是外层查询已输出的列别名，可直接引用。

修改后的完整SQL如下：

select
       current.id as currentId,
       current.date as currentDate,
       current.position as currentPosition,
       current.relevant_url as currentRelevantUrl,
       current.restrictions_probability as currentRestrictionsProbability,
       current.url_changed as currentUrlChanged,
       current.identifier_id as currentIdentifierId,
       current.phrase_id as phraseId,
       previous.id as previousId,
       previous.date as previousDate,
       previous.position as previousPosition,
       previous.relevant_url as previousRelevantUrl,
       previous.restrictions_probability as previousRestrictionsProbability,
       previous.url_changed as previousUrlChanged,
       previous.identifier_id as previousIdentifierId,
       semantics_clusters.id as clusterId,
       semantics_clusters.name as clusterName,
       site_pages.id as pageId,
       site_pages.url as pageUrl,
       site_pages.site_id as siteId,
       site_sites.url as siteUrl,
       semantics_core_phrases.frequency as frequency
from (
select
       id,
       date,
       position,
       relevant_url,
       restrictions_probability,
       url_changed,
       identifier_id,
       phrase_id
from overoptimisation
where identifier_id = 1) current

left join (
select
       id,
       date,
       position,
       relevant_url,
       restrictions_probability,
       url_changed,
       identifier_id,
       phrase_id
from overoptimisation
where identifier_id = 1) previous

on current.phrase_id = previous.phrase_id
inner join semantics_core_phrases on current.phrase_id = semantics_core_phrases.id
inner join semantics_clusters on semantics_core_phrases.cluster_id = semantics_clusters.id
inner join site_pages on semantics_clusters.page_id = site_pages.id
inner join site_sites on site_pages.site_id = site_sites.id
-- 新增排序子句，默认升序，如需降序可在字段后加 DESC
ORDER BY siteId, pageId, clusterId, frequency;

补充说明 #

• 默认排序规则为升序（ASC），若需对特定字段降序排序，在字段名后追加DESC关键字即可，示例：ORDER BY siteId DESC, pageId, clusterId DESC, frequency;
• PostgreSQL支持在ORDER BY中直接引用SELECT列表定义的列别名，无需重复编写原始表的字段路径。

内容的提问来源于stack exchange，提问作者Kifsif