小编典典

无法解决复杂的SQL删除查询问题

sql

情况

我的目标是每年进行一次cronjob，以根据年龄从数据库中删除某些数据。我拥有Bash和MySQL的强大功能。我从编写bash脚本开始，但是后来让我吃惊的是，我可以只用一个SQL查询就可以完成所有工作。

我本质上是一名程序员，并且我对数据结构没有太多的经验，所以这就是为什么我需要一些帮助。

表/数据结构

此查询的相关表和列如下：

登记：

+-----+-------------------+
| Id  | Registration_date |
+-----+-------------------+
|   2 | 2011-10-03        | 
|   3 | 2011-10-06        | 
|   4 | 2011-10-07        | 
|   5 | 2011-10-07        | 
|   6 | 2011-10-10        | 
|   7 | 2011-10-13        | 
|   8 | 2011-10-14        | 
|   9 | 2011-10-14        | 
|  10 | 2011-10-17        |
+-------------------------+

AssociatedClient：

+-----------+-----------------+
| Client_id | Registration_id |
+-----------+-----------------+
|         2 |               2 | 
|         3 |               2 | 
|         3 |               4 | 
|         4 |               5 | 
|         3 |               6 | 
|         5 |               6 | 
|         3 |               8 | 
|         8 |               9 | 
|         7 |              10 | 
+-----------------------------+

客户：此处仅涉及ID。

如您所见，这是一个简单的多对多关系。一个客户可以在他的名字上有多个注册，并且一个注册可以有多个客户。

目标

我需要删除5年内没有新注册的客户的所有注册和客户数据。听起来很简单，对吧？

棘手的部分

如果来自特定客户的 任何其他客户 的任何注册在5年内有新的注册，则应保留数据。

因此，假设客户A拥有4个注册，其中只有他一个，而他本人和客户B有1个注册。所有5个注册都超过5年。如果客户B在5年内没有新的注册，则应删除所有内容：客户A的注册和记录。如果B在5年内
确实进行了新注册，则应保留所有客户A的数据，包括他自己的旧注册。

我尝试过的

建立我的查询，我到此为止：

DELETE * FROM `Registration` AS Reg
WHERE TIMESTAMPDIFF(YEAR, Reg.`Registration_date`, NOW()) >= 5
AND 
    (COUNT(`Id`) FROM `Registration` AS Reg2
     WHERE Reg2.`Id` IN (SELECT `Registration_id` FROM `AssociatedClient` AS Clients
                         WHERE Clients.`Client_id` IN (SELECT `Client_id` FROM `AssociatedClient` AS Clients2
                                                       WHERE Clients2.`Registration_id` IN -- stuck
               #I need all the registrations from the clients associated with the first
               # (outer) registration here, that are newer than 5 years.

    ) = 0 -- No newer registrations from any associated clients

请理解，我在SQL方面的经验非常有限。我意识到，即使到目前为止，我也可以进行大量优化（使用joins等），甚至可能不正确。

我陷入困境的原因是，如果我可以使用某种循环，那么我想到的解决方案就可以使用，而且我只是意识到，在这种SQL查询中，您不容易做到这一点。

任何帮助

非常感谢。

阅读 156

2021-04-22

共1个答案

小编典典

首先确定注册的其他客户的注册。这是一个视图：

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加入该表中。

2021-04-22