MySQL作为列连接多个行

Say I have two tables in a MySQL Database.

假设在MySQL数据库中有两个表。

Table 1:

表1:

ID    Name
1     Jim
2     Bob

Table 2:

表2:

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

When selecting rows from the database, is there any way to join rows from the second table as columns to the first table? Currently I am using SELECT * FROM Table1 NATURAL JOIN Table2.

在从数据库中选择行时，是否有任何方法可以将第二表的行作为列连接到第一个表?目前我正在使用SELECT * FROM Table1 NATURAL JOIN Table2。

This outputs:

这个输出:

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

Currently I am sorting through this in my PHP script to sort it into an array. This is a pain, as I have to look at the IDs and see if they're the same and then sort accordingly. I feel like there is a way to do this right in MySQL, without having to sort it into an array in the PHP. There can be an unlimited number of entries in the second table for each ID.

目前，我正在我的PHP脚本中对其进行排序，以便将其排序为数组。这很痛苦，因为我必须查看id，看看它们是否相同，然后进行相应的排序。我觉得有一种方法可以在MySQL中实现，而不需要在PHP中对其进行排序。每个ID在第二个表中可以有无限个条目。

The desired result right from the MySQL query is:

来自MySQL查询的期望结果是:

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

I can't make columns for Race1, Race2 etc in the table themselves because there can be an unlimited number of races for each ID.

我不能在表中为Race1、Race2等创建列，因为每个ID可以有无限的race数。

Thanks for any help!

感谢任何帮助!

1 个解决方案

#1

An INNER JOIN will suffice your needs. MySQL has no PIVOT function by you can still simulate it using CASE and MAX() function.

一个内在的连接将满足你的需要。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

But if you have unknown number of RACE, then a DYNAMIC SQL is much more preferred.

但是如果您有未知的种族，那么动态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;

#1