MySQL объединяет несколько строк в виде столбцов

Скажем, у меня есть две таблицы в базе данных MySQL.

Таблица 1:

ID    Name
1     Jim
2     Bob

Таблица 2:

ID    Place    Race_Number
1     2nd      1
1     3rd      2
1     4th      3
2     1st      1
2     2nd      2
2     2nd      3

При выборе строк из базы данных есть ли способ присоединиться к строкам из второй таблицы как столбцы к первой таблице? В настоящее время я использую SELECT * FROM Table1 NATURAL JOIN Table2.

Выводится:

ID   Name    Place    Race_Number
1    Jim     2nd      1
1    Jim     3rd      2
1    Jim     4th      3
2    Bob     1st      1
2    Bob     2nd      2
2    Bob     2nd      3

В настоящее время я сортирую это в своем PHP script, чтобы отсортировать его в массиве. Это боль, поскольку я должен посмотреть на идентификаторы и посмотреть, совпадают ли они, а затем сортировать соответственно. Я чувствую, что есть способ сделать это прямо в MySQL, без необходимости сортировать его в массив в PHP. Для каждого идентификатора может быть неограниченное количество записей во второй таблице.

Желаемый результат прямо из запроса MySQL:

ID    Name    Race1    Race2    Race3
1     Jim     2nd      3rd      4th
2     Bob     1st      2nd      2nd

Я не могу создавать столбцы для Race1, Race2 и т.д. в самой таблице, потому что для каждого идентификатора может быть неограниченное количество рас.

Спасибо за любую помощь!

Ответы

Ответ 1

An INNER JOIN будет удовлетворять ваши потребности. MySQL не имеет функции PIVOT, вы все еще можете имитировать ее с помощью функций CASE и MAX().

SELECT  a.ID, a.NAME,
        MAX(CASE WHEN b.Race_Number = 1 THEN b.Place ELSE NULL END) Race1,
        MAX(CASE WHEN b.Race_Number = 2 THEN b.Place ELSE NULL END) Race2,
        MAX(CASE WHEN b.Race_Number = 3 THEN b.Place ELSE NULL END) Race3
FROM    Table1 a
        INNER JOIN Table2 b
            ON a.ID = b.ID
GROUP   BY a.ID, a.Name

Но если у вас есть неизвестное число RACE, то более предпочтительным является DYNAMIC SQL.

SET @sql = NULL;
SELECT
    GROUP_CONCAT(DISTINCT
    CONCAT('MAX(CASE WHEN b.Race_Number = ', Race_Number,
      ' THEN b.Place END) AS ', CONCAT('`Race', Race_Number, '`'))
    ) INTO @sql
FROM Table2;

SET @sql = CONCAT('SELECT s.Student_name, ', @sql, ' 
                   FROM Table1 a
                   LEFT JOIN Table2 b 
                        ON ON a.ID = b.ID
                   GROUP   BY a.ID, a.Name');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;