Ответ 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. Я думаю, что правило "удалить клиента и все, что он зарегистрировал". Если это так, я бы, вероятно, записывал идентификаторы регистрации во временную таблицу и записывал удаления для Registration и AssociatedClient, присоединяясь к ней.

Ответ 2

Вы хотите знать все регистрации, которые необходимо сохранить. Таким образом, ваш первый запрос возвращает регистрации в течение 5 предыдущих лет:

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

Затем вы можете удалить строки клиентов без регистрации, соответствующие критериям, используя следующий запрос:

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

и всех регистраций, отсутствующих в AssociatedClient:

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

Ответ 3

Использовать временные таблицы.

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

Ответ 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

Вы должны изучить функцию с использованием циклов. Это единственное, о чем я могу сейчас подумать.

Ответ 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

Ответ 6

Вам нужно два оператора sql delete, потому что вы удаляете из двух таблиц.

Оба оператора delete должны различать регистрацию, которая хранится, и те, которые удаляются, поэтому удаление из регистрационной таблицы должно происходить во втором.

Контрольная проблема - это самая последняя регистрация, связанная с идентификатором (идентификатор регистрации или идентификатор клиента). Таким образом, вы будете агрегировать на основе идентификатора и найти максимальную дату регистрации.

При удалении идентификаторов клиентов вы удаляете те, где общий идентификатор регистрации старше пяти лет. Это удаление приведет к отключению идентификаторов регистрации, которые были ранее связаны, но это нормально, потому что это действие не даст им более позднюю связанную дату регистрации.

Тем не менее, после того, как у вас есть идентификаторы клиентов, вам потребуется присоединиться к идентификаторам регистрации, которые обнаруживают связанные идентификаторы регистрации. Вам нужно будет присоединиться к идентификаторам клиентов, а затем самостоятельно присоединиться к идентификаторам регистрации, чтобы заставить эту часть работать правильно. Если вы удалили все идентификаторы клиентов, которые были связаны с регистрацией, вам также необходимо удалить эти регистрации.

Мой sql немного ржавый, и мой mysql rustier, и это непроверенный код, но это должно быть достаточно близко к тому, что, по вашему мнению, вам нужно сделать:

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

Надеюсь, вы не возражаете, чтобы я напомнил вам, но вам нужно сначала запустить инструкции select без удаления, и проверить результат на достоверность, прежде чем вы начнете и удалите материал.

(И если у вас есть какие-либо ограничения или индексы, которые мешают этому работать, вам также придется иметь дело с ними.)