PostgreSQL聚合与单独列出数据的查询需求求助

1. 合并用户的所有关联公司 #

首先将users表和users_companies表的用户-公司数据合并，得到每个用户的全部关联公司条目：

SELECT id AS user_id, company FROM users
UNION ALL
SELECT user_id, company FROM users_companies

2. 生成用户的聚合公司行 #

利用PostgreSQL的STRING_AGG函数，对每个用户的所有关联公司进行字符串拼接：

SELECT user_id, STRING_AGG(company, ',' ORDER BY company) AS company
FROM (
    SELECT id AS user_id, company FROM users
    UNION ALL
    SELECT user_id, company FROM users_companies
) AS all_user_companies
GROUP BY user_id

3. 合并结果并排序 #

将单独的公司条目和聚合条目合并，同时保证每个用户的单独行在前、聚合行在后：

WITH all_user_companies AS (
    SELECT id AS user_id, company FROM users
    UNION ALL
    SELECT user_id, company FROM users_companies
),
user_company_aggregates AS (
    SELECT user_id, STRING_AGG(company, ',' ORDER BY company) AS company
    FROM all_user_companies
    GROUP BY user_id
)
SELECT user_id, company FROM all_user_companies
UNION ALL
SELECT user_id, company FROM user_company_aggregates
ORDER BY user_id, 
         CASE WHEN company LIKE '%,%' THEN 1 ELSE 0 END;

关键说明 #

• 用WITH子句拆分逻辑，让查询结构更清晰；
• UNION ALL保留所有原始行，若需去重可替换为UNION；
• CASE表达式用于排序，确保单个公司条目排在聚合条目之前；
• STRING_AGG中的ORDER BY company保证拼接字符串的顺序一致，无需特定顺序可删除该部分。

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