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 scoreFROM table_nameWHERE id IN (1,2,3)UNION ALLSELECT '' 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 LucyFROM 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来生成查询语句。
