sql - PostgreSQL 选择通过多对多数据透视连接的表的多个列

Question

我有这个问题:

SELECT
    a.account_uuid,
    a.account_no,
    a.account_group_uuid,
    a.account_scope_uuid,
    a.created_at,
    a.deleted_at,
    s.service_uuid,
    s.status,
    st.service_type,
(
    SELECT
            c.company
    FROM companies c
    WHERE a.company_owner_uuid = c.company_uuid   
)
FROM
    accounts a 
LEFT JOIN
    services s 
ON a.account_uuid = s.account_uuid 
LEFT JOIN
    service_types st 
ON s.service_type_uuid = st.service_type_uuid
WHERE
    a.deleted_at IS NULL
ORDER BY
    a.account_no

我需要通过数据透视表 accounts_contacts 加入并从 people 表中选择多个列，该表将包含 account_uuid 和 person_uuid。 accounts_contacts 表中还有 is_primary 和 is_active 列，一次只有一个主列，因此最终结果将是单一的名字和姓氏。这是查询的思路:

SELECT
    p.first_name, p.last_name 
FROM
    people p 
INNER JOIN
    accounts_contacts ac 
ON ac.account_uuid = a.account_uuid 
AND ac.person_uuid = p.person_uuid 
WHERE
    ac.is_primary = true 
AND ac.is_active = true

但不确定如何将其放入上述查询中。子查询只允许其中一列。

Answer 1

account_contacts 是一个“关联”或“联结”表。它不是数据透视表。

基本思想应该是join:

SELECT . . . ,
       p.first_name, p.last_name 
FROM accounts a LEFT JOIN
     services s 
     ON a.account_uuid = s.account_uuid LEFT JOIN
     service_types st 
     ON s.service_type_uuid = st.service_type_uuid LEFT JOIN
     accounts_contacts ac 
     ON ac.account_uuid = a.account_uuid LEFT JOIN
     people p
     ON ac.person_uuid = p.person_uuid AND
        ac.is_primary = true AND
        ac.is_active = true