MySQL行轉(zhuǎ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=0 DEFAULT CHARSET=utf8;
INSERT INTO `sdfs`.`student`(`ID`, `USER_NAME`, `COURSE`, `SCORE`) VALUES (10, '張三', '數(shù)學(xué)', 34);
INSERT INTO `sdfs`.`student`(`ID`, `USER_NAME`, `COURSE`, `SCORE`) VALUES (11, '張三', '語(yǔ)文', 58);
INSERT INTO `sdfs`.`student`(`ID`, `USER_NAME`, `COURSE`, `SCORE`) VALUES (12, '張三', '英語(yǔ)', 58);
INSERT INTO `sdfs`.`student`(`ID`, `USER_NAME`, `COURSE`, `SCORE`) VALUES (13, '李四', '數(shù)學(xué)', 45);
INSERT INTO `sdfs`.`student`(`ID`, `USER_NAME`, `COURSE`, `SCORE`) VALUES (14, '李四', '語(yǔ)文', 87);
INSERT INTO `sdfs`.`student`(`ID`, `USER_NAME`, `COURSE`, `SCORE`) VALUES (15, '李四', '英語(yǔ)', 45);
INSERT INTO `sdfs`.`student`(`ID`, `USER_NAME`, `COURSE`, `SCORE`) VALUES (16, '王五', '數(shù)學(xué)', 76);
INSERT INTO `sdfs`.`student`(`ID`, `USER_NAME`, `COURSE`, `SCORE`) VALUES (17, '王五', '語(yǔ)文', 34);
INSERT INTO `sdfs`.`student`(`ID`, `USER_NAME`, `COURSE`, `SCORE`) VALUES (18, '王五', '英語(yǔ)', 89);
SELECT
user_name,
max( CASE course WHEN "數(shù)學(xué)" THEN score ELSE 0 END ) 數(shù)學(xué),
max( CASE course WHEN "語(yǔ)文" THEN score ELSE 0 END ) 語(yǔ)文,
max( CASE course WHEN "英語(yǔ)" THEN score ELSE 0 END ) 英語(yǔ)
FROM
student
GROUP BY
user_name;
SELECT DISTINCT
t.user_name,
( SELECT a.score FROM student a WHERE t.USER_NAME = a.USER_NAME AND a.COURSE = "數(shù)學(xué)" ) 數(shù)學(xué),
( SELECT a.score FROM student a WHERE t.USER_NAME = a.USER_NAME AND a.COURSE = "語(yǔ)文" ) 語(yǔ)文,
( SELECT a.score FROM student a WHERE t.USER_NAME = a.USER_NAME AND a.COURSE = "英語(yǔ)" ) 英語(yǔ)
FROM
student t
