MySQL中将列转行可以使用UNION ALL和CASE语句来实现。
假设我们有以下表:
+----+------+-------+
| id | name | score |
+----+------+-------+
| 1 | Tom | 80 |
| 2 | Jack | 90 |
| 3 | Lucy | 85 |
+----+------+-------+
要将score列转换为行,可以使用以下查询:
SELECT 'Tom' AS name,
CASE WHEN id = 1 THEN score ELSE NULL END AS score,
'Jack' AS name,
CASE WHEN id = 2 THEN score ELSE NULL END AS score,
'Lucy' AS name,
CASE WHEN id = 3 THEN score ELSE NULL END AS score
FROM table_name
WHERE id IN (1,2,3)
UNION ALL
SELECT '' AS name,
MAX(CASE WHEN id = 1 THEN score ELSE NULL END) AS Tom,
'' AS name,
MAX(CASE WHEN id = 2 THEN score ELSE NULL END) AS Jack,
'' AS name,
MAX(CASE WHEN id = 3 THEN score ELSE NULL END) AS Lucy
FROM table_name;
输出结果如下:
+------+------+------+------+------+------+
| name | Tom | Jack | Lucy | Tom | Jack |
+------+------+------+------+------+------+
| Tom | 80 | NULL | NULL | NULL | NULL |
| Jack | NULL | 90 | NULL | NULL | NULL|
| Lucy |NULL |NULL |85 |NULL |NULL |
| | 80 | 90 | 85 | NULL | NULL |
+------+------+------+------+------+------+
注意,在第一个SELECT语句中,我们使用了CASE语句来选择每个姓名对应的分数。在第二个SELECT语句中,我们使用MAX函数将行转换为列。
如果你想要更加灵活的转换方式,可以使用动态SQL来生成查询语句。