如何通过Union合并查询并按指定规则排序去重标签？

可以通过给不同来源的标签添加标识字段，结合去重和排序逻辑实现需求。修改后的SQL如下：

SELECT tag_name
FROM (
    SELECT DISTINCT ON (tag_name) 
           unnest(system_tags) AS tag_name, 
           1 AS source
    FROM "models"
    LEFT JOIN projects ON projects.id = models.project_id
    WHERE projects.is_public = true
    
    UNION ALL
    
    SELECT unnest(tags) AS tag_name, 
           2 AS source
    FROM "models"
    LEFT JOIN projects ON projects.id = models.project_id
    WHERE projects.is_public = true
) AS combined_tags
ORDER BY source, tag_name;

逻辑说明 #

1. 标记来源优先级：给system_tags的标签标记source=1，tags的标签标记source=2，明确两类标签的排序优先级。
2. 去重并保留高优先级条目：使用DISTINCT ON (tag_name)确保每个标签仅出现一次，且优先保留来自system_tags的条目（因为source=1在排序中优先级更高）。
3. 按规则排序：外层查询通过ORDER BY source, tag_name，先按来源优先级排列（system_tags在前），再对同一来源的标签按字母序排序。

如果你的PostgreSQL版本不支持DISTINCT ON，可以用分组方式替代：

SELECT tag_name
FROM (
    SELECT unnest(system_tags) AS tag_name, 1 AS source
    FROM "models"
    LEFT JOIN projects ON projects.id = models.project_id
    WHERE projects.is_public = true
    
    UNION ALL
    
    SELECT unnest(tags) AS tag_name, 2 AS source
    FROM "models"
    LEFT JOIN projects ON projects.id = models.project_id
    WHERE projects.is_public = true
) AS combined_tags
GROUP BY tag_name
ORDER BY MIN(source), tag_name;

这个版本通过GROUP BY tag_name去重，MIN(source)获取每个标签的最高优先级来源，再按优先级和字母序完成排序。

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