如何选择具有多个值的人员

Question

我想选择有两个值的人(激活&表中的循环)，例如

table::tbl_transactions

<表类> id 名称 操作 tbody><<tr>1约翰激活2约翰的3Salah激活4比尔激活5比尔的6比尔的

您可以聚合每个用户name的action值，并检查数组是否为2长(因为您只需要2个操作)并包含['activate', 'recurring'](因为您只需要这些值)

SELECT t.id, t.name FROM tbl_transactions t
JOIN LATERAL (
SELECT 
name, 
ARRAY_AGG(action) AS actions
FROM tbl_transactions
GROUP BY name
) user_actions ON t.name = user_actions.name 
AND ARRAY_LENGTH(actions, 1) = 2
AND ARRAY['activate', 'recurring']::VARCHAR[] @> actions

演示

这里有一个查询，用于只选择出现两次的名称:

select t.* 
from t 
join (select name, count(*) 
from t 
having count(*) = 2
) c on t.name = c.name ;

查询如下所示。检查Sql Fiddle。您可以使用count distinct action来计数不同的动作值，检查它是否大于1。

select a.* from tbl_transactions a join 
(select name, count(*) from tbl_transactions group by name having count(distinct action) > ) b 
on a.name = b.name

我将使用bool_or()窗口函数:

with complete_check as (
select id, name, action,
bool_or(action = 'recurring') over w as has_recurring,
bool_or(action = 'activate') over w as has_activate
from tbl_transactions
window w as (partition by name)
)
select id, name, action
from complete_check
where has_recurring and has_activate;

db<此处小提琴>