create view groups as
select a.Client_id
, c.Registration_id
from AssociatedClient as a
join AssociatedClient as b on a.Registration_id = b.Registration_id
join AssociatedClient as c on b.Client_id = c.Client_id;
这给了我们：
select Client_id
, min(Registration_id) as first
, max(Registration_id) as last
, count(distinct Registration_id) as regs
, count(*) as pals
from groups
group by Client_id;
Client_id first last regs pals
---------- ---------- ---------- ---------- ----------
2 2 8 4 5
3 2 8 4 18
4 5 5 1 1
5 2 8 4 5
7 10 10 1 1
8 9 9 1 1
当然，您不需要视图。这只是为了方便。您可以只使用一个虚拟表。但是要仔细检查它，以使自己确信它为每个客户提供了正确的“朋友注册”范围。请注意，该视图 未 引用Registration。这很重要，因为即使我们使用它从中删除Registration，它也会产生相同的结果，因此我们可以将其用于第二个delete语句。
现在，我们有了客户及其“朋友注册”的列表。每个朋友最后一次注册的日期是什么？
select g.Client_id, max(Registration_date) as last_reg
from groups as g join Registration as r
on g.Registration_id = r.Id
group by g.Client_id;
g.Client_id last_reg
----------- ----------
2 2011-10-14
3 2011-10-14
4 2011-10-07
5 2011-10-14
7 2011-10-17
8 2011-10-14
哪一个在某个时间之前有最新日期？
select g.Client_id, max(Registration_date) as last_reg
from groups as g join Registration as r
on g.Registration_id = r.Id
group by g.Client_id
having max(Registration_date) < '2011-10-08';
g.Client_id last_reg
----------- ----------
4 2011-10-07
IIUC意味着应该删除客户端＃4，并且应该删除他注册的所有内容。注册将是
select * from Registration
where Id in (
select Registration_id from groups as g
where Client_id in (
select g.Client_id
from groups as g join Registration as r
on g.Registration_id = r.Id
group by g.Client_id
having max(Registration_date) < '2011-10-08'
)
);
Id Registration_date
---------- -----------------
5 2011-10-07
而且，可以肯定的是，客户端＃4已在注册＃5中，并且是该测试中唯一要删除的客户端。
从那里您可以计算出这些delete语句。我认为规则是“删除客户及其注册的任何东西”。如果是这样，我可能会将注册ID写入一个临时表中，然后将两者都删除，Registration并AssociatedClient加入该表中。
602392714
清零编程群