mysql语句讲行转换为列
时间: 2023-07-24 19:31:08 浏览: 43
您可以使用MySQL内置的PIVOT函数将行转换为列。
例如,如果您有以下表格:
```
+----+-------+------+
| id | name | score |
+----+-------+------+
| 1 | Alice | 100 |
| 2 | Bob | 80 |
| 3 | Alice | 90 |
| 4 | Bob | 70 |
+----+-------+------+
```
您可以使用以下查询将其转换为列:
```
SELECT
name,
MAX(CASE WHEN id = 1 THEN score ELSE NULL END) AS 'score_1',
MAX(CASE WHEN id = 2 THEN score ELSE NULL END) AS 'score_2',
MAX(CASE WHEN id = 3 THEN score ELSE NULL END) AS 'score_3',
MAX(CASE WHEN id = 4 THEN score ELSE NULL END) AS 'score_4'
FROM
table_name
GROUP BY
name;
```
这将生成以下结果:
```
+-------+---------+---------+---------+---------+
| name | score_1 | score_2 | score_3 | score_4 |
+-------+---------+---------+---------+---------+
| Alice | 100 | 0 | 90 | 0 |
| Bob | 0 | 80 | 0 | 70 |
+-------+---------+---------+---------+---------+
```
这里我们使用了MAX函数来将行转换为列。如果有多个行具有相同的名称和ID,则使用MAX函数将它们合并在一起。