MySQL 将多行作为列加入 [英] MySQL Join Multiple Rows as Columns

问题描述

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

Say I have two tables in a MySQL Database.

表 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.

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.

输出:

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 脚本中对此进行排序，以将其排序为一个数组.这很痛苦，因为我必须查看 ID 并查看它们是否相同，然后相应地进行排序.我觉得有一种方法可以在 MySQL 中正确执行此操作，而无需将其排序到 PHP 中的数组中.每个 ID 在第二个表中可以有无限数量的条目.

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.

MySQL 查询的期望结果是:

The desired result right from the MySQL query is:

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

我无法在表格中为 Race1、Race2 等创建列，因为每个 ID 可以有无限数量的种族.

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.

感谢您的帮助！

推荐答案

INNER JOIN 将满足您的需求.MySQL没有PIVOT函数，你仍然可以使用CASE和MAX()函数来模拟它.

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

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 会更受欢迎.

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

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;

这篇关于MySQL 将多行作为列加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！