mysql里列转行 和 行转列 怎么写

MySQL 中进行列转行和行转列可以使用 `CASE WHEN` 和 `GROUP BY` 语句。

列转行：

假设有以下表格：

+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 |  a   |  b   |  c   |
|  2 |  d   |  e   |  f   |
+----+------+------+------+

我们想要将其转换为以下形式：

+-------+-------+
|  col  | value |
+-------+-------+
| col1  |   a   |
| col1  |   d   |
| col2  |   b   |
| col2  |   e   |
| col3  |   c   |
| col3  |   f   |
+-------+-------+

可以使用以下 SQL 语句：

SELECT 'col1' AS col, col1 AS value FROM table_name
UNION ALL
SELECT 'col2' AS col, col2 AS value FROM table_name
UNION ALL
SELECT 'col3' AS col, col3 AS value FROM table_name;

行转列：

假设有以下表格：

+----+-------+-------+
| id |  col  | value |
+----+-------+-------+
|  1 | col1  |   a   |
|  2 | col1  |   d   |
|  3 | col2  |   b   |
|  4 | col2  |   e   |
|  5 | col3  |   c   |
|  6 | col3  |   f   |
+----+-------+-------+

我们想要将其转换为以下形式：

+----+------+------+------+
| id | col1 | col2 | col3 |
+----+------+------+------+
|  1 |  a   |  b   |  c   |
|  2 |  d   |  e   |  f   |
+----+------+------+------+

可以使用以下 SQL 语句：

SELECT id,
       MAX(CASE WHEN col = 'col1' THEN value END) AS col1,
       MAX(CASE WHEN col = 'col2' THEN value END) AS col2,
       MAX(CASE WHEN col = 'col3' THEN value END) AS col3
FROM table_name
GROUP BY id;