Mysql行列转化实例

DROP TABLE IF EXISTS tmp_user;
CREATE TABLE tmp_user(
	id INT (11) NOT NULL auto_increment,
	name VARCHAR(20) COMMENT '姓名',
	PRIMARY KEY (id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

DROP TABLE IF EXISTS tmp_subject;
CREATE TABLE tmp_subject(
	id INT (11) NOT NULL auto_increment,
	subject VARCHAR(20) COMMENT '科目',
	PRIMARY KEY (id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

DROP TABLE IF EXISTS tmp_score;
CREATE TABLE tmp_score (
	id INT(11) NOT NULL auto_increment,
	userid int(10) NOT NULL COMMENT '用户id',
	subjectid int(10) COMMENT '科目id',
	score DECIMAL(18,2) COMMENT '成绩',
	PRIMARY KEY (id)
) ENGINE = INNODB DEFAULT CHARSET = utf8;


INSERT INTO tmp_user(name) VALUES ('张三');
INSERT INTO tmp_user(name) VALUES ('李四');
INSERT INTO tmp_user(name) VALUES ('王五');
INSERT INTO tmp_user(name) VALUES ('赵六');

INSERT INTO tmp_subject(subject) VALUES ('语文');
INSERT INTO tmp_subject(subject) VALUES ('数学');
INSERT INTO tmp_subject(subject) VALUES ('英语');

INSERT INTO tmp_score(userid,subjectid,score) VALUES (1,1,94);
INSERT INTO tmp_score(userid,subjectid,score) VALUES (1,2,52);
INSERT INTO tmp_score(userid,subjectid,score) VALUES (1,3,83);
INSERT INTO tmp_score(userid,subjectid,score) VALUES (2,1,68);
INSERT INTO tmp_score(userid,subjectid,score) VALUES (2,2,93);
INSERT INTO tmp_score(userid,subjectid,score) VALUES (2,3,100);
INSERT INTO tmp_score(userid,subjectid,score) VALUES (3,1,100);
INSERT INTO tmp_score(userid,subjectid,score) VALUES (3,2,94);
INSERT INTO tmp_score(userid,subjectid,score) VALUES (3,3,92);
INSERT INTO tmp_score(userid,subjectid,score) VALUES (4,1,56);
INSERT INTO tmp_score(userid,subjectid,score) VALUES (4,2,74);
INSERT INTO tmp_score(userid,subjectid,score) VALUES (4,3,69);

-- 原始数据
select cs1.id,u.name,cs2.subject,cs1.score FROM tmp_score AS cs1
INNER JOIN tmp_user as u on u.id = cs1.userid
INNER JOIN tmp_subject AS cs2 on cs2.id = cs1.subjectid;

-- SUM CASE 方式转化
SELECT u.name AS '姓名',
SUM(CASE cs1.subjectid WHEN 1 THEN cs1.score ELSE 0 END) AS '语文',
SUM(CASE cs1.subjectid WHEN 2 THEN cs1.score ELSE 0 END) AS '数学',
SUM(CASE cs1.subjectid WHEN 3 THEN cs1.score ELSE 0 END) AS '英语'
FROM tmp_score AS cs1
INNER JOIN tmp_user as u on u.id = cs1.userid
INNER JOIN tmp_subject as cs2 on cs2.id = cs1.subjectid
GROUP BY u.id;

-- SUM IF 方式转化
SELECT u.name AS '姓名',
SUM(IF(cs1.subjectid=1,cs1.score,0)) AS '语文',
SUM(IF(cs1.subjectid=2,cs1.score,0)) AS '数学',
SUM(IF(cs1.subjectid=3,cs1.score,0)) AS '英语'
FROM tmp_score AS cs1
INNER JOIN tmp_user as u on u.id = cs1.userid
INNER JOIN tmp_subject as cs2 on cs2.id = cs1.subjectid
GROUP BY u.id;

-- GROUP_CONCAT 方式转化
SELECT u.name AS '姓名',
GROUP_CONCAT(cs2.subject,':',cs1.score) as '成绩'
FROM tmp_score AS cs1
INNER JOIN tmp_user as u on u.id = cs1.userid
INNER JOIN tmp_subject as cs2 on cs2.id = cs1.subjectid
GROUP BY u.id;