mysql行转列函数|极客笔记

mysql行转列函数

在MySQL数据库中，行转列是一种常见的数据转换操作。它允许将行中的多个列转换为单个列，或者将单个列转换为多个列。这种操作通常用于数据报表生成、数据分析和数据展示等场景。为了实现行转列操作，我们可以使用MySQL中的一些内置函数。

本文将详细介绍MySQL中的行转列函数，并提供一些示例来说明如何使用这些函数。

1. GROUP_CONCAT函数

GROUP_CONCAT函数是MySQL中用于将多行数据合并为一个字符串的函数。它可以将指定列中的多个值连接起来，并用指定的分隔符分隔。

语法

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

参数说明

expr: 需要合并的列或表达式。
DISTINCT: 可选参数，表示在合并过程中去重。
ORDER BY: 可选参数，指定合并后的结果排序方式。
SEPARATOR: 可选参数，指定合并后结果的分隔符，默认为逗号。

示例

假设我们有一张商品销售记录表（orders），其中包含了商品ID、月份和销售额三个列。我们希望将每个月份对应的销售额合并成一个字符串，并以逗号分隔。

SELECT month, GROUP_CONCAT(sales_amount ORDER BY month) AS sales_amounts
FROM orders
GROUP BY month;

运行结果：

month	sales_amounts
1	5000,6000,7000
2	5500,4500,8000,3000
3	4000,3500,7500

2. CASE语句

CASE语句是MySQL中用于条件判断的语句。在行转列中，我们可以使用CASE语句根据条件将一个列的多个值转换为多个新列。

语法

CASE case_value
    WHEN when_value THEN result
    [WHEN when_value THEN result ...]
    [ELSE result]
END

参数说明

case_value: 需要判断的表达式或列。
when_value: 需要匹配的值。
result: 匹配成功后返回的结果。
ELSE: 可选参数，匹配失败时的默认返回结果。

示例

假设我们有一张学生成绩表（scores），其中包含学生ID、科目和分数三个列。我们希望将每个科目的平均分和最高分分别作为新的列。

SELECT subject,
    AVG(CASE WHEN student_id = 1 THEN score END) AS avg_score_student_1,
    MAX(CASE WHEN student_id = 1 THEN score END) AS max_score_student_1,
    AVG(CASE WHEN student_id = 2 THEN score END) AS avg_score_student_2,
    MAX(CASE WHEN student_id = 2 THEN score END) AS max_score_student_2
FROM scores
GROUP BY subject;

运行结果：

subject	avg_score_student_1	max_score_student_1	avg_score_student_2	max_score_student_2
Math	90	95	80	88
English	85	92	78	89
Science	92	98	85	94

3. UNPIVOT操作

UNPIVOT操作是将列转换为行的一种操作，与行转列相反。MySQL并没有直接提供UNPIVOT函数，但我们可以使用UNION操作和CASE语句来实现类似效果。

示例

假设我们有一张学生成绩表（scores），其中包含学生ID、数学、英语和科学三个科目的分数列。我们希望将每个学生对应的科目和分数展示成行的形式。

SELECT student_id, 'Math' AS subject, math_score AS score FROM scores
UNION ALL
SELECT student_id, 'English' AS subject, english_score AS score FROM scores
UNION ALL
SELECT student_id, 'Science' AS subject, science_score AS score FROM scores;

运行结果：

student_id	subject	score
1	Math	95
1	English	92
1	Science	98
2	Math	88
2	English	89
2	Science	94

4. PIVOT操作

PIVOT操作是将行转换为列的一种操作，与UNPIVOT相反。MySQL并没有直接提供PIVOT函数，但我们可以使用GROUP BY和CASE语句来实现类似效果。

示例

假设我们有一张学生成绩表（scores），其中包含学生ID、科目和分数三个列。我们希望将每个学生对应的科目和分数转换为每个学生的一行。

SELECT student_id,
    MAX(CASE WHEN subject = 'Math' THEN score END) AS math_score,
    MAX(CASE WHEN subject = 'English' THEN score END) AS english_score,
    MAX(CASE WHEN subject = 'Science' THEN score END) AS science_score
FROM scores
GROUP BY student_id;

运行结果：