查找过去两年内在另一个表中没有新引用的所有条目

Question

我有以下三个表：

CREATE TABLE group (
id SERIAL PRIMARY KEY,
name VARCHAR NOT NULL,
insert_date TIMESTAMP WITH TIME ZONE NOT NULL
);
CREATE TABLE customer (
id SERIAL PRIMARY KEY,
ext_id VARCHAR NOT NULL,
insert_date TIMESTAMP WITH TIME ZONE NOT NULL
);
CREATE TABLE customer_in_group (
id SERIAL PRIMARY KEY,
customer_id INT NOT NULL,
group_id INT NOT NULL,
insert_date TIMESTAMP WITH TIME ZONE NOT NULL,
CONSTRAINT customer_id_fk
FOREIGN KEY(customer_id)
REFERENCES customer(id),
CONSTRAINT group_id_fk
FOREIGN KEY(group_id)
REFERENCES group(id)
)

我需要找到在过去两年内没有任何customer_in_group实体的group_id列引用它们的所有组。然后，我计划删除所有引用它们的customer_in_groups，并在找到它们后最终删除该组。

因此，基本上给定以下两个组和以下3个customer_in_groups

Group
| id | name   | insert_date              |
|----|--------|--------------------------|
| 1  | group1 | 2011-10-05T14:48:00.000Z |
| 2  | group2 | 2011-10-05T14:48:00.000Z |
Customer In Group
| id | group_id | customer_id | insert_date              |
|----|----------|-------------|--------------------------|
| 1  | 1        | 1           | 2011-10-05T14:48:00.000Z |
| 2  | 1        | 1           | 2020-10-05T14:48:00.000Z |
| 3  | 2        | 1           | 2011-10-05T14:48:00.000Z |

我只想回到group2，因为group1有一个customer_in_group引用了过去两年插入的内容。

我不确定如何编写查询来查找所有这些组。

Answer 1

作为初学者，我建议在customer_in_group的外键上启用on delete cascade。

然后，您可以从groups中删除所需的行，它将删除子表中的依赖行。为此，您可以使用not exists:

delete from groups g
where not exists (
select 1
from customer_in_group cig
where cig.group_id = g.id and cig.insert_date >= now() - interval '2 year'
)