mysql字符串列传行函数_mysql 行转列，列传行

建立一个学生成绩表

CREATE TABLE studentScores (

username VARCHAR (20),

subjects VARCHAR (30),

score FLOAT

);

INSERTINTO[StudentScores]SELECT'Nick','语文',80

INSERTINTO[StudentScores]SELECT'Nick','数学',90

INSERTINTO[StudentScores]SELECT'Nick','英语',70

INSERTINTO[StudentScores]SELECT'Nick','生物',85

INSERTINTO[StudentScores]SELECT'Kent','语文',80

INSERTINTO[StudentScores]SELECT'Kent','数学',90

INSERTINTO[StudentScores]SELECT'Kent','英语',70

INSERTINTO[StudentScores]SELECT'Kent','生物',85

SELECT

username,

max(CASE subjects WHEN '语文' THEN score ELSE0END) AS '语文',

max(CASE subjectsWHEN '数学' THEN scoreELSE0END) AS '数学',

max(CASE subjectsWHEN '英语' THEN scoreELSE0END) AS '英语',

max(CASE subjectsWHEN '生物' THEN scoreELSE0END) AS '生物'

FROM

studentscores

GROUP BY

username;