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屋!