拆分选择结果

Question

我有下一个查询：

SELECT login_id
FROM invite
WHERE sender_id in (111, 222) AND is_valid IS TRUE

结果是下一个：

login_id
4205
4154
9

我需要我们将"login_id"拆分为相关的"sender_id"。在我的情况下，结果应该是下一个：

111    222
4205   9
4154

Answer 1

您可以使用条件聚合，但您需要一个列来聚合。您可以使用ROW_NUMBER():

SELECT MAX(login_id) FILTER (WHERE sender_id = 111) as sender_111,
MAX(login_id) FILTER (WHERE sender_id = 222) as sender_222       
FROM (SELECT i.*, ROW_NUMBER() OVER (PARTITION BY sender_id ORDER BY sender_id) as seqnum
FROM invite i
) i
WHERE sender_id in (111, 222) AND is_valid IS TRUE
GROUP BY seqnum;

注意：您可能会发现以数组的形式在单行中返回结果更方便：

select array_agg(login_id) filter (where sender_id = 111) as sender_111,
array_agg(login_id) filter (where sender_id = 222) as sender_222
from invite i
where sender_id in (111, 222) and is_valid;