postgresql - postgres 聚合不显示 LEFT JOIN 表有空值的行

Question

我有一个表，其中列出了一个成员的电子邮件地址，并且有一个 bool 值 is_selected。想法是只应选择一个。我使用 is_selected 上的 where 子句将表连接到自身。我要

string_agg(u.email, ',')

在 View 中的一列/行中显示所有未选择的电子邮件。

Here is a fiddle .

我的问题是，在没有未选中的条目的情况下，我无法使 View 工作。

sql fiddle 今天有问题所以:

CREATE TABLE member_email
  (
    member integer NOT NULL, -- reference to another table
    email character varying(150) NOT NULL,
    is_selected boolean NOT NULL,
    PRIMARY KEY(member,email)
  );

INSERT INTO member_email 
    (member,email,is_selected) 
    VALUES 
        (2,'dax@example.com',TRUE),
        (2,'oldemail@example.com',FALSE),
        (2,'prevemail@example.com',FALSE),
        (3,'rick@example.com',TRUE),
        (3,'richard@example.com',FALSE),
        (4,'bob@example.com',TRUE);

CREATE VIEW v_member_email AS 
    SELECT s.member
    ,s.email as selected_email
    ,string_agg(u.email, ',') as unselected_email 
    FROM member_email s 
    LEFT JOIN member_email u 
        ON s.member = u.member 
    WHERE s.is_selected = TRUE 
        AND u.is_selected = FALSE 
    GROUP BY s.member,s.email 
    ORDER BY member;

SELECT * FROM v_member_email;

-- where is bob@example.com in result?

Answer 1

SQL Fiddle

如果右侧的条件放在 where 子句中，您会将 left join 转换为 inner join。只需将其移动到连接条件即可:

select
    s.member
    ,s.email as selected_email
    ,string_agg(u.email, ',') as unselected_email 
from
    member_email s 
    left join
    member_email u on
        s.member = u.member
        and not u.is_selected
where s.is_selected
group by s.member,s.email 
order by member;