一、行转列
即将原本同一列下多行的不同内容作为多个字段,输出对应内容。
建表语句
DROP TABLE IF EXISTS tb_score;
CREATE TABLE tb_score
(
id INT(11) NOT NULL auto_increment,
userid VARCHAR(20) NOT NULL COMMENT '用户id',
subject VARCHAR(20) COMMENT '科目',
score DOUBLE COMMENT '成绩',
PRIMARY KEY (id)
) ENGINE = INNODB
DEFAULT CHARSET = utf8;
插入数据
INSERT INTO tb_score(userid, subject, score)
VALUES ('001', '语文', 90);
INSERT INTO tb_score(userid, subject, score)
VALUES ('001', '数学', 92);
INSERT INTO tb_score(userid, subject, score)
VALUES ('001', '英语', 80);
INSERT INTO tb_score(userid, subject, score)
VALUES ('002', '语文', 88);
INSERT INTO tb_score(userid, subject, score)
VALUES ('002', '数学', 90);
INSERT INTO tb_score(userid, subject, score)
VALUES ('002', '英语', 75.5);
INSERT INTO tb_score(userid, subject, score)
VALUES ('003', '语文', 70);
INSERT INTO tb_score(userid, subject, score)
VALUES ('003', '数学', 85);
INSERT INTO tb_score(userid, subject, score)
VALUES ('003', '英语', 90);
INSERT INTO tb_score(userid, subject, score)