行转列
数据准备
建表：
CREATE TABLE`student`(`ID`int(10)NOT NULL AUTO_INCREMENT,`USER_NAME`varchar(20)DEFAULT NULL,`COURSE`varchar(20)DEFAULT NULL,`SCORE`float DEFAULT'0',PRIMARY KEY(`ID`))ENGINE=InnoDB AUTO_INCREMENT=10DEFAULT CHARSET=utf8;
新增数据：
insertintostudent(USER_NAME,COURSE,SCORE)values("张三","数学",34),("张三","语文",58),("张三","英语",58),("李四","数学",45),("李四","语文",87),("李四","英语",45),("王五","数学",76),("王五","语文",34),("王五","英语",89);
源数据：
行转列图一
SELECTuser_name,MAX(CASEcourseWHEN'数学'THENscoreELSE0END)数学,MAX(CASEcourseWHEN'语文'THENscoreELSE0END)语文,MAX(CASEcourseWHEN'英语'THENscoreELSE0END)英语FROMstudentGROUPBYUSER_NAME;
行转列后：
行转列图二
列转行
数据准备
建表：
CREATE TABLE `grade`(`ID`int(10)NOTNULLAUTO_INCREMENT,`USER_NAME`varchar(20)DEFAULTNULL,`CN_SCORE`floatDEFAULTNULL,`MATH_SCORE`floatDEFAULTNULL,`EN_SCORE`floatDEFAULT'0',PRIMARY KEY(`ID`))ENGINE=InnoDB AUTO_INCREMENT=1DEFAULT CHARSET=utf8;
新增数据：
insertintograde(USER_NAME,CN_SCORE,MATH_SCORE,EN_SCORE)values("张三",34,58,58),("李四",45,87,45),("王五",76,34,89);
源数据：
列转行图一.jpg
selectuser_name,'语文'COURSE,CN_SCOREasSCOREfromgradeunionselectuser_name,'数学'COURSE,MATH_SCOREasSCOREfromgradeunionselectuser_name,'英语'COURSE,EN_SCOREasSCOREfromgradeorder by user_name,COURSE;
列转行后：
列转行图二.jpg
链接：https://www.jianshu.com/p/5a2dae144238
