mysql行转列函数

MySQL中行转列可以通过使用GROUP_CONCAT函数和CASE WHEN语句来实现。

例如，假设我们有以下表：

+----+-------+-------+
| id | name  | grade |
+----+-------+-------+
| 1  | Alice | A     |
| 2  | Bob   | B     |
| 3  | Tom   | C     |
| 4  | Jack  | A     |
+----+-------+-------+

现在我们希望将成绩（grade）列转换为列名，并将每个学生的成绩填入相应的列中，得到以下结果：

+----+-------+------+------+------+
| id | name  | A    | B    | C    |
+----+-------+------+------+------+
| 1  | Alice | 1    | 0    | 0    |
| 2  | Bob   | 0    | 1    | 0    |
| 3  | Tom   | 0    | 0    | 1    |
| 4  | Jack  | 1    | 0    | 0    |
+----+-------+------+------+------+

我们可以使用以下SQL语句实现：

SELECT id, name, 
CASE WHEN grade = 'A' THEN 1 ELSE 0 END AS A,
CASE WHEN grade = 'B' THEN 1 ELSE 0 END AS B,
CASE WHEN grade = 'C' THEN 1 ELSE 0 END AS C
FROM your_table

然后，我们可以使用GROUP BY语句将结果按照id和name分组，然后使用SUM函数将每个学生的成绩合并为一个记录：

SELECT id, name, SUM(A) AS A, SUM(B) AS B, SUM(C) AS C
FROM (
    SELECT id, name, 
    CASE WHEN grade = 'A' THEN 1 ELSE 0 END AS A,
    CASE WHEN grade = 'B' THEN 1 ELSE 0 END AS B,
    CASE WHEN grade = 'C' THEN 1 ELSE 0 END AS C
    FROM your_table
) AS t
GROUP BY id, name