PostgreSQL：如果其中至少有一行不在列表中，则排除行

Question

我有以下简单的表格

<表类> COL1 COL2 COL3 tbody><<tr>U1L1XU1L5XU2乐队L2XU3L2XU4L4XU4哈佛XU5地级X

如何使用一个简单的聚合函数实现您想要的功能的完整示例:

create table test (col1 text, col2 text, col3 text);
insert into test values ('U1', 'L1', 'X');
insert into test values ('U1', 'L5', 'X');
insert into test values ('U2', 'L2', 'X');
insert into test values ('U3', 'L2', 'X');
insert into test values ('U4', 'L4', 'X');
insert into test values ('U4', 'L6', 'X');
insert into test values ('U5', 'L7', 'X');

select
col1
from (
select 
col1,
-- aggregate all occurences for each value in col1 and see if any is missing, if so this will be false
bool_and(col2 in ('L1', 'L2', 'L3', 'L4')) as has_value
from test 
where col3 = 'X'
group by col1
) agg
where has_value;

CREATE temp TABLE test102 (
col1 text,
col2 text,
col3 text
);
INSERT INTO test102
VALUES ('U1', 'L1', 'X'),
('U1', 'L5', 'X'),
('U2', 'L2', 'X'),
('U3', 'L2', 'X'),
('U4', 'L4', 'X'),
('U4', 'L6', 'X'),
('U5', 'L7', 'X'),
('U2', 'L2', 'X');
(
SELECT
COL1
FROM
test102 t
WHERE
t.COL3 = 'X'
AND t.COL2 IN ('L1', 'L2', 'L3', 'L4'))
EXCEPT ALL (
SELECT
COL1
FROM
test102 t
WHERE
t.COL3 = 'X'
AND t.COL2 NOT IN ('L1', 'L2', 'L3', 'L4'));

except all return duplicate, except distinct remove duplicate。