行轉(zhuǎn)列

數(shù)據(jù)準(zhǔn)備

建表：

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;

新增數(shù)據(jù)：

insertintostudent(USER_NAME,COURSE,SCORE)values("張三","數(shù)學(xué)",34),("張三","語文",58),("張三","英語",58),("李四","數(shù)學(xué)",45),("李四","語文",87),("李四","英語",45),("王五","數(shù)學(xué)",76),("王五","語文",34),("王五","英語",89);

源數(shù)據(jù)：

行轉(zhuǎn)列圖一

SELECTuser_name,MAX(CASEcourseWHEN'數(shù)學(xué)'THENscoreELSE0END)數(shù)學(xué),MAX(CASEcourseWHEN'語文'THENscoreELSE0END)語文,MAX(CASEcourseWHEN'英語'THENscoreELSE0END)英語FROMstudentGROUPBYUSER_NAME;

行轉(zhuǎn)列后：

行轉(zhuǎn)列圖二

列轉(zhuǎn)行

數(shù)據(jù)準(zhǔn)備

建表：

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;

新增數(shù)據(jù)：

insertintograde(USER_NAME,CN_SCORE,MATH_SCORE,EN_SCORE)values("張三",34,58,58),("李四",45,87,45),("王五",76,34,89);

源數(shù)據(jù)：

列轉(zhuǎn)行圖一.jpg

selectuser_name,'語文'COURSE,CN_SCOREasSCOREfromgradeunionselectuser_name,'數(shù)學(xué)'COURSE,MATH_SCOREasSCOREfromgradeunionselectuser_name,'英語'COURSE,EN_SCOREasSCOREfromgradeorder by user_name,COURSE;

列轉(zhuǎn)行后：

列轉(zhuǎn)行圖二.jpg

鏈接：http://www.itdecent.cn/p/5a2dae144238