mysql怎么更改纵向变横向排列 |mysql 行限制

首先，大家来看一个示例表。

+----+---------+-------+-------+
| ID |  NAME   | FOO_1 | FOO_2 |
+----+---------+-------+-------+
|  1 | Alice   |   100 |    50 |
|  2 | Bob     |    75 |    60 |
|  3 | Charlie |    80 |    90 |
+----+---------+-------+-------+

如果大家想要将纵向的FOO_1和FOO_2转化为横向的数据，可以采用以下步骤：

第一步：使用GROUP BY语句将数据按照NAME字段分组

SELECT NAME FROM my_table GROUP BY NAME;

第二步：使用SUM、CASE和GROUP BY语句将数据转换为横向

SELECT
NAME,
SUM(CASE WHEN ID=1 THEN FOO_1 ELSE 0 END) AS FOO_1_1,
SUM(CASE WHEN ID=2 THEN FOO_1 ELSE 0 END) AS FOO_1_2,
SUM(CASE WHEN ID=3 THEN FOO_1 ELSE 0 END) AS FOO_1_3,
SUM(CASE WHEN ID=1 THEN FOO_2 ELSE 0 END) AS FOO_2_1,
SUM(CASE WHEN ID=2 THEN FOO_2 ELSE 0 END) AS FOO_2_2,
SUM(CASE WHEN ID=3 THEN FOO_2 ELSE 0 END) AS FOO_2_3
FROM my_table
GROUP BY NAME;

该语句将输出以下结果：

+---------+---------+---------+---------+---------+---------+---------+
|  NAME   | FOO_1_1 | FOO_1_2 | FOO_1_3 | FOO_2_1 | FOO_2_2 | FOO_2_3 |
+---------+---------+---------+---------+---------+---------+---------+
| Alice   |     100 |       0 |       0 |      50 |       0 |       0 |
| Bob     |       0 |      75 |       0 |       0 |      60 |       0 |
| Charlie |       0 |       0 |      80 |       0 |       0 |      90 |
+---------+---------+---------+---------+---------+---------+---------+

通过这种方式，大家可以将纵向的数据转换为更易于分析和使用的横向数据。