难以理解复杂的SQL删除查询

Question

情况

我的目标是设置一个每年运行的定时任务，根据数据的年龄从数据库中删除某些数据。我可以使用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        |
+-------------------------+ 

关联客户端：

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

客户端：只有 ID 在这里是相关的。

正如您所见，这是一个简单的多对多关系。一个客户可以有多个与他相关的注册信息，而一个注册信息可以有多个客户。

目标

我需要删除那些在过去5年内没有新注册信息的客户及其所有注册信息。听起来很简单，对吧？

棘手的部分

只要任何其他客户在任何一个注册信息中有新的注册信息，数据就应该被保留。

因此，假设客户A有4个仅包含他自己的注册信息，以及1个自己和客户B的注册信息。这五个注册信息都超过了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的经验非常有限。我意识到即使我已经得到了一些内容，也可以进行大量优化（使用连接等），并且可能甚至不正确。

我卡住的原因是我心中的解决方案需要使用某种循环，而我才刚刚意识到这在这种类型的SQL查询中并不容易实现。

任何帮助都将不胜感激。

- MarioDS

---

“一个注册可以拥有多个客户端”：您提供的名为 AssociatedClient 的表中的示例数据并没有展示这一点。如果我假设以下两条记录可以同时存在于 AssociatedClient 表中，那么我的理解是否正确：(2,2), (3,2) ? - RandomSeed

@Yak 是的，这只是我复制时的巧合，我会将它更改为包含您的示例，这确实是有效的。 - MarioDS

如果客户A和客户B共享一个注册ID，客户B和客户C共享一个注册ID，并且只有客户C在过去5年中拥有注册，那么是否应该保留客户A，因为它与客户B相关联？如果是这样，这种递归关系可以有多深？ - GarethD

@GarethD 为了简单起见，我希望看到一个仅适用于1级（即仅客户端A和B）的解决方案。实际上，您所描述的情况可能很少发生。现在请忽略它，我稍后会处理，但如果您能提供适用于所有级别的解决方案，那将是一个巨大的奖励。 - MarioDS

抱歉我的前一个回答有误，我已经删除了它。根据这个问题：https://dev59.com/PnVD5IYBdhLWcg3wOpBh 问题是MySQL不允许在删除的WHERE子句中使用SELECT。我想你可以使用两个步骤的解决方案 - 使用类似于我的查询模式的SELECT将要删除的ID放入临时表中，然后执行DELETE * FROM REGISTRATION WHERE REGISTRATION.ID IN TEMPTABLE。 - Rich Tolley

@RichTolley 那可能会有用，但我决定使用bash。处理mysql结果可能不是理想的选择，但这比试图弄清楚查询要少麻烦。 - MarioDS

Answer 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         

当然，您不需要视图，它只是为了方便而已。您可以使用虚拟表。但是请仔细检查以确信它为每个客户端生成了正确的“ pal注册”范围。请注意，该视图并没有引用“ Registration ”。这很重要，因为即使在我们使用它从“ Registration ”中删除后，它仍会产生相同的结果，因此我们可以将其用于第二个删除语句。

现在，我们有一个客户端及其“ pal注册”的列表。每个“ pal ”最后一次注册的日期是什么？

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

如果我理解正确，那就意味着客户编号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的删除。

- James K. Lowden

Answer 2

您希望了解所有需要保留的注册信息。因此，您的第一个查询将返回过去五年内的注册信息：

SELECT
  Id
FROM
  Registration
WHERE
  Registration_date >= '2011-10-08'

然后，所有与先前查询相关的客户端注册信息：

SELECT
  a2.Registration_id as Id
FROM
  AssociatedClient AS a1
  INNER JOIN AssociatedClient AS a2
    ON a1.Client_id = a2.Client_id 
WHERE
  a1.Registration_id IN
  (  
    SELECT
      Id
    FROM
      Registration
    WHERE
      Registration_date >= '2011-10-08'
 )

然后，您可以通过将前面的查询组合在一个UNION中来得到您不能删除的所有注册信息，并且您希望得到不属于此查询的所有客户：

SELECT
  Client_id
FROM
  AssociatedClient
WHERE
  Registration_id NOT IN
  (
    SELECT
      Id
    FROM
      Registration
    WHERE
      Registration_date >= '2011-10-08'
    UNION
    SELECT
      a2.Registration_id as Id
    FROM
      AssociatedClient AS a1
      INNER JOIN AssociatedClient AS a2
        ON a1.Client_id = a2.Client_id 
    WHERE
      a1.Registration_id IN
      (  
        SELECT
          Id
        FROM
          Registration
        WHERE
          Registration_date >= '2011-10-08'
      )
  )

您可以在此SQL fiddle中查看结果。

然后，您可以使用以下查询删除未按照标准进行注册的客户端行：

DELETE FROM
  AssociatedClient
WHERE
  Client_id IN (<previous query>);

所有未在AssociatedClient中注册的注册信息：

DELETE FROM
  Registration
WHERE
  Id NOT IN (SELECT Registration_id FROM AssociatedClient)

- psadac

---

+1 是因为采用另一种方式（选择我想要保留的内容），并使用 UNION 运算符，这是我经常忘记的运算符。 - MarioDS

Answer 3

你需要两个 SQL 删除语句，因为你要从两个表中删除。

两个删除语句都需要区分正在保留和正在删除的注册信息，所以从注册表中删除需要在第二步进行。

控制问题是与 ID（注册 ID 或客户端 ID）相关联的最近注册。因此，你将根据 ID 进行聚合，并找到最大的注册日期。

当删除客户端 ID 时，你将删除那些聚合注册 ID 超过五年的客户端 ID。这种删除将取消之前链接的注册 ID，但这没关系，因为此操作不会给它们更近期的关联注册日期。

话虽如此，一旦你有了客户端 ID，就需要在注册 ID 上进行连接，找到关联的注册 ID。你需要连接到客户端 ID，然后自我连接回注册 ID，以使其正常工作。如果你已经删除了所有与注册相关联的客户端 ID，则还需要删除那些注册。

我的 SQL 有点生疏，我的 MySQL 更生疏，而且这是未经测试的代码，但这应该相当接近我认为你需要做的事情：

delete from associatedclient where client_id in (
  select client_id from (
    select ac.client_id, max(r.registration_date) as dt
      from associatedclient ac
        inner join registration r
          on ac.registration_id = r.id
      group by ac.client_id
  ) d where d.dt < cutoff
)

下一步大致如下所示：

delete from registration where id in (
  select id from (
    select r1.id, max(r2.date) dt
      from registration r1
        inner join associated_client ac1
          on r1.id = ac1.registration_id
        inner join associated_client ac2
          on ac1.client_id = ac2.client_id
        inner join registration r2
          on ac2.registration_id = r2.id
) d
  where d.dt < cutoff
  or d.dt is null

希望你不介意我提醒一下，你应该先运行选择语句而不是删除语句，并检查结果的可信性，然后再继续删除操作。

（如果有任何约束或索引阻止此操作，请处理这些问题。）

- rdm

Answer 4

这应该可以为您提供链接客户端的下一级正确客户信息。我知道这可能无法提供您所需的所有信息。但是，如评论中所述，一个1级实现现在应该足够了。这可能不是最优的。

SELECT
AC1.Client_id,
MAX(R.Registration_date) AS [LatestRegistration]
FROM
#AssociatedClient AC1
JOIN #AssociatedClient AC2
    ON  AC1.Registration_id = AC2.Registration_id
JOIN #AssociatedClient AC3
    ON  AC2.Client_id = AC3.Client_id
JOIN #Registration R
    ON  AC3.Registration_id = R.Id
GROUP BY
AC1.Client_id

你应该研究一下使用循环的函数。这是我现在能想到的唯一方法。

- user1801742

Answer 5

我是一个SQL Server的人，但我认为这个语法也适用于MySQL。这个查询将会提取那些不应该被删除的客户。

SELECT A3.Client_id
FROM AssociatedClient A1
#Get clients with registrations in the last 5 years
JOIN Registration R1 ON A1.Registration_id = R1.Id 
    AND TIMESTAMPDIFFERENCE(YEAR, R1.Registration_Date, Now()) <= 5
#get the rest of the registrations for those clients
JOIN AssociatedClient A2 ON A1.Client_id = A2.Client_id
#get other clients tied to the rest of the registrations
JOIN AssociatedClient A3 ON A2.Registration_id = A3.Registration_id

- Jim

Answer 6

使用临时表。

INSERT INTO LockedClient(client_id) --select clients that should not be deleted
SELECT DISTINCT ac.client_id 
FROM AssociatedClient ac
JOIN Registration r ON r.Id = ac.ID
WHERE TIMESTAMPDIFF(YEAR, Reg.`Registration_date`, NOW()) >= 5;

DELETE  * FROM Registration r -- now delete all except locked clients
JOIN AssociatedClient ac ON ac.registration_id = r.id
LEFT JOIN LockedClient lc ON lc.client_id = ac.client_id
WHERE TIMESTAMPDIFF(YEAR, Reg.`Registration_date`, NOW()) >= 5 AND lc.client_id IS NULL

- ijrandom

---

抱歉，这不是一个真正的解决方案。整个问题在于找到不应被删除的客户端Id。你只是告诉我一旦我得到它们该怎么做。 - MarioDS