数据表:
第一种方法:CASE WHEN THEN ELSE END
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`
;
结果如下:
- max—聚合函数 取最大值
- as 语文—别名作为列名
- max函数可以改成SUM-----求和函数
合并字段显示:利用group_cancat(course,”:”,”score”)
SELECT
`name`,
GROUP_CONCAT(course, ":", score) AS 成绩
FROM
student
GROUP BY
`name`;
第二种方法:IF()语句
SELECT 'name',
MAX(IF(course='语文',score,0)) AS 语文,
MAX(IF(course='数学',score,0)) AS 数学,
MAX(IF(course='英语',score,0)) AS 英语
FROM
student
GROUP BY `name`