mysql简单的行转列

MySQL中行转列操作可以使用PIVOT函数或者CASE WHEN语句来实现。具体实现方法如下：

1. PIVOT函数

PIVOT函数可以将一列数据转换成多列，使用方法如下：

SELECT 
    [Grouping Column], 
    [Pivot Column 1], [Pivot Column 2],..., [Pivot Column N]
FROM
    [Source Table]
PIVOT
(
    [Aggregation Function]([Value Column])
    FOR [Pivot Column] IN ([Pivot Column 1], [Pivot Column 2],..., [Pivot Column N])
) AS [Pivot Table];

其中，[Grouping Column]是需要分组的列名，[Pivot Column 1], [Pivot Column 2],..., [Pivot Column N]是需要转换的列名，[Aggregation Function]是聚合函数（如SUM、AVG等），[Value Column]是需要聚合的值列名，[Source Table]是源数据表名，[Pivot Table]是输出的表名。

例如，将学生的成绩按科目进行转换：

SELECT 
    name,
    MAX(CASE WHEN subject = 'Math' THEN score ELSE NULL END) AS Math,
    MAX(CASE WHEN subject = 'English' THEN score ELSE NULL END) AS English,
    MAX(CASE WHEN subject = 'Science' THEN score ELSE NULL END) AS Science
FROM 
    scores
GROUP BY 
    name;

2. CASE WHEN语句

使用CASE WHEN语句可以将多个行转换成一行，使用方法如下：

SELECT 
    [Grouping Column],
    MAX(CASE WHEN [Condition 1] THEN [Value 1] ELSE NULL END) AS [Pivot Column 1],
    MAX(CASE WHEN [Condition 2] THEN [Value 2] ELSE NULL END) AS [Pivot Column 2],
    ...,
    MAX(CASE WHEN [Condition N] THEN [Value N] ELSE NULL END) AS [Pivot Column N]
FROM 
    [Source Table]
GROUP BY 
    [Grouping Column];

其中，[Grouping Column]是需要分组的列名，[Condition 1], [Condition 2],..., [Condition N]是条件语句，[Value 1], [Value 2],..., [Value N]是需要转换的列名，[Source Table]是源数据表名。

例如，将学生的成绩进行转换：

SELECT 
    name,
    MAX(CASE WHEN subject = 'Math' THEN score ELSE NULL END) AS Math,
    MAX(CASE WHEN subject = 'English' THEN score ELSE NULL END) AS English,
    MAX(CASE WHEN subject = 'Science' THEN score ELSE NULL END) AS Science
FROM 
    scores
GROUP BY 
    name;