Как просмотреть результаты и удалить все, кроме самого нового элемента?

У меня есть БД с игроками и событиями. У одного игрока много событий. Событие имеет дату создания.

Мы хотим очистить БД, мне нужно написать SQL (используя MySQL), который для каждого игрока удалит все его события, кроме самого нового.

Итак, как мне сделать такой цикл и пользовательское удаление в SQL:

select all PLAYERS from PLAYER
for each PLAYER
delete all EVENTS except where EVENT.creationDate is the newest one

?

ЛЮБАЯ помощь очень ценится в этом!

более детально

Таблица PLAYER имеет PLAYER_ID varchar (100)

В таблице EVENT есть EVENT_ID bigint, PLAYER_ID varchar(100), CREATED_AT (bigint)


mysql sql sql-delete
person Community    schedule 07.12.2011    source источник
comment
Более подробная информация о полях в таблице событий поможет нам помочь вам... Например, название столбца идентификатора игрока и наличие в таблице приличного уникального ключа.   -  person Jamie F    schedule 07.12.2011
comment
Привет Джейми, добавил больше деталей! Спасибо!   -  person    schedule 07.12.2011
comment
удалить события e1, где e1.created_at != (выберите max(e2.created_at) из событий e2, где e1.player_id = e2.player_id) для тестового запуска выберите * из событий e1, где e1.created_at != (выберите max(e2.. ..) изучить sql намного проще и чище, чем java   -  person Ender Wiggin    schedule 07.12.2011
comment
спасибо, как мне обернуть этот вызов удаления внутри select * from player, чтобы он делал это для каждого игрока?   -  person    schedule 07.12.2011

Ответы (4)


arrow_upward
1
arrow_downward

Возможно, вы захотите использовать синтаксис удаления с несколькими таблицами в сочетании с подзапросом для создания справочной таблицы, в которой вы можете проверить новейшую дату создания:

DELETE FROM `events` 
  USING `events` 
    INNER JOIN (
      SELECT playerId, MAX(creationDate) AS `maxCreationDate` 
      FROM `events` GROUP BY playerId) AS `referenceTable` 
    USING (`playerID`) 
  WHERE `events`.`playerId` = `referenceTable`.`playerId` 
    AND `events`.`creationDate` != `referenceTable`.`maxCreationDate`;

Протестировано с этими таблицами:

mysql> SELECT * FROM players;
+----------+------------+
| playerId | playerName |
+----------+------------+
|        1 | Andy       |
|        2 | Buddy      |
+----------+------------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM events;
+---------+----------+---------------------+
| eventId | playerId | creationDate        |
+---------+----------+---------------------+
|       3 |        1 | 2011-12-06 01:49:58 |
|       1 |        1 | 2011-12-07 01:49:20 |
|       2 |        1 | 2011-12-08 01:50:03 |
|       1 |        2 | 2011-12-07 01:50:06 |
|       2 |        2 | 2011-12-08 01:50:00 |
+---------+----------+---------------------+
5 rows in set (0.00 sec)

И результат выглядит хорошо.

mysql> DELETE FROM `events` 
    -> USING `events` 
    -> INNER JOIN (
    -> SELECT playerId, MAX(creationDate) AS `maxCreationDate` 
    -> FROM `events` GROUP BY playerId
    -> ) AS `referenceTable` 
    -> USING (`playerID`) 
    -> WHERE `events`.`playerId` = `referenceTable`.`playerId` 
    -> AND `events`.`creationDate` != `referenceTable`.`maxCreationDate`;
Query OK, 3 rows affected (0.03 sec)

mysql> SELECT * FROM events;
+---------+----------+---------------------+
| eventId | playerId | creationDate        |
+---------+----------+---------------------+
|       2 |        1 | 2011-12-08 01:50:03 |
|       2 |        2 | 2011-12-08 01:50:00 |
+---------+----------+---------------------+
2 rows in set (0.00 sec)
person Riyono    schedule 07.12.2011
comment
спасибо, я хотел бы сначала попробовать это с выбором, чтобы убедиться, что нужные вещи будут удалены, но когда я меняю DELETE FROM на SELECT * FROM, USING, кажется, вызывает синтаксические ошибки - есть советы? - person ; 07.12.2011
comment
Если вы хотите сначала проверить это, измените DELETE FROM events USING на SELECT * FROM - person Riyono; 07.12.2011
comment
Я пытаюсь выполнить ваш запрос в SQL Explorer, но он сообщает мне о синтаксических ошибках вокруг ключевого слова USING. Любые идеи? - person ; 09.12.2011

arrow_upward
3
arrow_downward

MySQL поддерживает удаление нескольких таблиц как расширение SQL. Вы можете выполнять соединения без использования подзапросов.

DELETE e1 
FROM Players p 
INNER JOIN Events e1 ON p.player_id = e1.player_id
INNER JOIN Events e2 ON p.player_id = e2.player_id
WHERE e1.event_id < e2.event_id;

Мы хотим удалить строку e1, если существует другая строка e2 с более высоким идентификатором события и тем же игроком. Естественно, самое последнее событие для данного игрока никогда не может пройти условие для e1.

Я предполагаю, что event_id увеличивается по мере увеличения createDate, и лучше выбрать это, поскольку это никогда не приведет к ничьей.

person Bill Karwin    schedule 07.12.2011
comment
@Algorithmist: Извините, запрос в вашем ответе, похоже, удаляет из неправильной таблицы. Вопрос об удалении событий, а не игроков. Кроме того, MySQL не любит, когда вы DELETE из таблицы и SELECT из нее в одном выражении - он не знает, какую блокировку использовать. - person Bill Karwin; 07.12.2011
comment
К вашему сведению, это работало для небольших объемов данных, но с более чем 20 000 записей запрос становится экспоненциально медленным. Может быть, он сравнивает идентификатор каждой записи с 20 000 других идентификаторов для каждой строки, поэтому он плохо масштабируется? В конце концов я использовал решение @Riyono ниже. Спасибо. - person ; 22.12.2011
comment
Этот запрос, как и большинство соединений, зависит от наличия правильного индекса. Я предлагаю составной индекс событий по двум столбцам (player_id,event_id) именно в таком порядке. - person Bill Karwin; 22.12.2011

arrow_upward
1
arrow_downward

в каждом разделе:

ВЫБЕРИТЕ Events.creationDate FROM Events WHERE Events.player == (каждый игрок) ORDER BY DESC LIMIT 1; // получите первое событие

Поместите его в переменную типа DateLastEvent или около того.

УДАЛИТЬ события, ГДЕ Events.creationDate ‹ DateLastEvent:

//за исключением случаев, когда строго в тот же день/время он удалит все остальные

person Gorkam    schedule 07.12.2011
comment
Спасибо! Любая идея, как бы я обернул это в цикл для каждого игрока, чтобы (каждый игрок) был установлен правильно? - person ; 07.12.2011
comment
у вас нет var на SQL, поэтому, если вы не хотите помещать сюда какой-либо код, вы должны имбрикировать SQL req - person Gorkam; 07.12.2011

arrow_upward
0
arrow_downward

Удалить из игроков, в которых нет event_id (выбрать event_id из игроков, где event_date в (выбрать min(event_date) из группы игроков по player_id))

Это простой запрос, который также будет работать.

person Algorithmist    schedule 07.12.2011
comment
Я действительно хочу удалить из событий, а не из игроков. - person ; 07.12.2011