mysql 列转行，合并字段

数据表

表结构.png

列转行：利用max(case when then)

SELECT
      `name`,
      MAX(
          CASE 
          WHEN  course='语文' THEN
              score
          END
      ) AS 语文,
      MAX(
          CASE 
          WHEN course='数学' THEN
              score
           END
      ) AS 数学, 
      MAX(
          CASE 
          WHEN course='英语' THEN
              score
          END
      ) AS 英语
FROM
      student
GROUP BY `name`;

输出结果.png

合并字段显示:利用group_cancat(course,”:”,”score”)

SELECT
    `name`,
    GROUP_CONCAT(course, ":", score) AS 成绩
FROM
    student
GROUP BY
    `name`;

输出结果.png