Объедините строки MYSQL, суммируя некоторые столбцы, обновляя идентификаторы в другой таблице

У меня есть база данных MySQL с несколькими таблицами, две из которых выглядят так:

игроки

+-----+---------+-------------------+----------------+----------------+--------------+---------------------+
| id  | base_id |       name        | total_searches | auctions_found | auctions_won |     created_at      |
+-----+---------+-------------------+----------------+----------------+--------------+---------------------+
| 283 |  177683 | Christian Eriksen |             12 |              2 |            1 | 2019-05-29 11:25:08 |
| 160 |  177683 | Christian Eriksen |             24 |              4 |            1 | 2019-05-29 11:25:08 |
|  76 |  345345 | Yan Sommer        |             12 |              1 |            3 | 2019-05-29 11:25:08 |
| 712 |    4353 | Yannick Carrasco  |             42 |              5 |           12 | 2019-05-29 11:25:08 |
+-----+---------+-------------------+----------------+----------------+--------------+---------------------+

транзакции

+----+-----------+---------------------+
| id | player_id |     updated_at      |
+----+-----------+---------------------+
|  1 |       283 | 2019-05-29 11:25:08 |
|  2 |       160 | 2019-05-29 11:25:08 |
|  3 |        76 | 2019-05-29 11:25:08 |
+----+-----------+---------------------+

Как видите, в таблице игроков есть дубликаты (Кристиан Эриксен). Я хотел бы объединить эти строки, суммируя столбцы total_searches, auctions_found и auctions_won. В этой таблице есть несколько других столбцов (некоторые здесь опущены для краткости), которые либо одинаковы (например, base_id), либо не имеют большого значения, какое значение сохраняется (например, created_at).

Что важно, так это id. Следует либо создать новый id, либо, в идеале, сохранить один из существующих id. Затем таблицу транзакций необходимо будет обновить с помощью нового player_id.

+-----+---------+-------------------+----------------+----------------+--------------+---------------------+
| id  | base_id |       name        | total_searches | auctions_found | auctions_won |     created_at      |
+-----+---------+-------------------+----------------+----------------+--------------+---------------------+
| 160 |  177683 | Christian Eriksen |             36 |              6 |            2 | 2019-05-29 11:25:08 |
|  76 |  345345 | Yan Sommer        |             12 |              1 |            3 | 2019-05-29 11:25:08 |
| 712 |    4353 | Yannick Carrasco  |             42 |              5 |           12 | 2019-05-29 11:25:08 |
+-----+---------+-------------------+----------------+----------------+--------------+---------------------+

+----+-----------+---------------------+
| id | player_id |     updated_at      |
+----+-----------+---------------------+
|  1 |       160 | 2019-05-29 11:25:08 |
|  2 |       160 | 2019-05-29 11:25:08 |
|  3 |        76 | 2019-05-29 11:25:08 |
+----+-----------+---------------------+

У меня нет никаких знаний SQL, поэтому я еще ничего не пробовал. Некоторые указатели или, в идеале, полное решение будут оценены.

mysql sql

29.05.2019 13:04

Освоение архитектуры микросервисов с Laravel: Лучшие практики, преимущества и советы для разработчиков

В последние годы архитектура микросервисов приобрела популярность как способ построения масштабируемых и гибких приложений. Laravel , популярный PHP...

Как построить CRUD-приложение в Laravel

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

Освоение PHP и управление базами данных: Создание собственной СУБД - часть II

В предыдущем посте мы создали функциональность вставки и чтения для нашей динамической СУБД. В этом посте мы собираемся реализовать функции обновления...

Документирование API с помощью Swagger на Springboot

В предыдущей статье мы уже узнали, как создать Rest API с помощью Springboot и MySql .

Роли и разрешения пользователей без пакета Laravel 9

Этот пост изначально был опубликован на techsolutionstuff.com .

Как установить LAMP Stack - Security 5/5 на виртуальную машину Azure Linux VM

В предыдущей статье мы завершили установку базы данных, для тех, кто не знает.

Перейти к ответу Данный вопрос помечен как решенный

Ответы 1

Ответ принят как подходящий

Это довольно сложно. Я бы предложил:

Сделайте резервную копию таблиц!
Обновите идентификатор в таблице транзакций, чтобы он был максимальным.
Исправьте таблицу игроков.
Исправьте модель данных.

То есть:

update transactions t join
       players p
       on t.player_id = p.id join
       (select p2.name, max(p2.id) as max_id
        from players p2
        group by p2.name
        having count(*) > 1
       ) p2
       on p2.name = p.name  -- or should this be "base_id"
    set t.player_id = p2.max_id
    where t.player_id <> p2.max_id;

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

create table players_temp as
    select max(id) as id,  base_id, name,
           sum(total_searches) as total_searches, 
           sum(auctions_found) as auctions_found,
           sum(auctions_won) as auctions_won,
           min(created_at) as created_at  
    from players
    group by base_id, name;  -- or whatever

truncate table players;

insert into players (id, base_id, name, total_searches, auctions_found, auctions_won, created_at)
    select id, base_id, name, total_searches, auctions_found, auctions_won, created_at
    from players_temp;

Затем исправьте модель данных:

alter table players add constraint unq_players_name
    unique (name);

alter table players add constraint unq_players_base_id
    unique (base_id);

29.05.2019 13:16

Другие вопросы по теме

Как выполнить команду ошибка-сбой через код SQL?

Повторно использовать вычисляемое поле в том же запросе

Преобразование некоторых данных базы данных (таблица SQL) в формат .csv

Как посчитать сотрудников, вовлеченных в процесс, и перечислить их имена в одной ячейке?

Рассчитать общее количество затраченных часов и общую стоимость на человека

Я запутался с этой базой данных, должен ли я создать больше внешних ключей или больше столбцов?

Группировка SQL с несколькими критериями столбца

Как создать новую таблицу, взяв столбцы из information_schema.columns с масштабом и точностью?

EXISTS не работает с подзапросом в предложении WITH

Вопрос о самостоятельных соединениях. Наблюдение за взаимодействием двух пользователей

Объедините строки MYSQL, суммируя некоторые столбцы, обновляя идентификаторы в другой таблице

Ответы 1

Другие вопросы по теме

Похожие вопросы