代码之家 › 专栏 › 技术社区 › Panda Naman

如何将MySQL连接行简化为列?

join mysql

-1

Panda Naman · 技术社区 · 7 年前

目前,我有一个班级主席成绩表。我正在尝试将行作为列连接。基本上,结果应该提供不同年份班长性别的概述。

表,命名为 results

+----+------+---------+--------+----------+-----------+-------+----------+-----------+
| id | year | faculty | winner | w_gender | w_percent | loser | l_gender | l_percent |
+----+------+---------+--------+----------+-----------+-------+----------+-----------+
|  1 | 2016 | Yellow  | Tom    | B        |        56 | Jill  | G        |        46 |
|  2 | 2016 | Green   | Susan  | G        |        52 | Sandy | G        |        48 |
|  3 | 2016 | Purple  | Carly  | G        |        51 | Jax   | B        |        49 |
|  4 | 2018 | Yellow  | Tom    | B        |        56 | Jill  | G        |        46 |
|  5 | 2018 | Green   | Ben    | B        |        52 | Sandy | G        |        48 |
|  6 | 2018 | Purple  | Amanda | G        |        52 | James | B        |        48 |
+----+------+---------+--------+----------+-----------+-------+----------+-----------+

预期结果:

+--------+------+------+
| group  | 2016 | 2018 |
+--------+------+------+
| yellow | B    | G    |
| green  | G    | G    |
| purple | G    | B    |
+--------+------+------+

工作 MySQL Join Multiple Rows as Columns :

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(CASE WHEN year = ', year, ' THEN w_gender END) AS ', CONCAT('`', year, '`')) ORDER BY year ASC) INTO @sql FROM results;
SET @sql = CONCAT('SELECT faculty, ', @sql, ' FROM results GROUP BY faculty');
PREPARE stmt FROM @sql;
EXECUTE stmt;

更新: year 列应该是动态的,即使我在未来几年添加更多结果,查询也应该可以工作。

1 回复 | 直到 7 年前

1

fubar 7 年前

您可以使用以下查询获得按年份聚合的结果。

SELECT 
    MAX(year) AS year, 
    MAX(IF(faculty = 'Yellow', w_gender, NULL)) AS yellow,
    MAX(IF(faculty = 'Green', w_gender, NULL)) AS green,
    MAX(IF(faculty = 'Purple', w_gender, NULL)) AS purple
FROM results
GROUP BY year

下面是一个简化的工作数据库小提琴: https://www.db-fiddle.com/f/uoX44nDLSji344iXCdmtfV/0