用于返回没有活动组成员身份的用户的SQL查询

Question

我有这个模式，它可以软删除/归档一个组：

create table users (id int, name varchar(255));
create table groups (id int, name varchar(255), archived_at datetime);
create table memberships (group_id int, user_id int);

insert into users (id, name) values 
  (1, 'no groups'),
  (2, 'only active groups'),
  (3, 'only archived groups'),
  (4, 'active and archived groups');

insert into groups (id, name, archived_at) values
  (1, 'active group', null),
  (2, 'archived group', '2021-04-13');

insert into memberships (user_id, group_id) values
  (2, 1),
  (3, 2),
  (4, 1),
  (4, 2);

我想查询那些“没有组”的用户:恰好是零个活动(非归档)组，加上零个或多个归档组。

-- How do I get this to return only users 1 and 3?
select *
from users u
left join memberships m on m.user_id = u.id
left join groups g on g.id = m.group_id
where
  m.group_id is null
  or 
  g.archived_at is not null

对于当前的模式，这是可能的吗？

https://www.db-fiddle.com/f/h1szvQmQHp1g84Ex9WvRHy/1

Answer 1

这听起来像not exists

select u.*
from users u
where not exists (select 1
                  from memberships m join
                       groups g
                       on m.group_id = g.id
                  where m.user_id = u.id and g.archived_at is null
                 );

据推测，活动组是尚未存档的组。