MySQL行转列

mysql创建表结构

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) VALUES ('003','政治',82);

查询得到

1. 列传行方法1：

   SELECT userid,
   SUM(CASE `subject` WHEN '语文' THEN score ELSE 0 END) as '语文',
   SUM(CASE `subject` WHEN '数学' THEN score ELSE 0 END) as '数学',
   SUM(CASE `subject` WHEN '英语' THEN score ELSE 0 END) as '英语',
   SUM(CASE `subject` WHEN '政治' THEN score ELSE 0 END) as '政治' 
   FROM tb_score 
   GROUP BY userid
   

   sql执行结果
   
2. 列传行方法2：

	SELECT userid,
	SUM(IF(`subject`='语文',score,0)) AS 语文,
	SUM(IF(`subject`='数学',score,0)) AS 数学,
	SUM(IF(`subject`='英语',score,0)) AS 英语,
	SUM(IF(`subject`='政治',score,0)) AS 政治
	FROM tb_score
	GROUP BY userid ;

3. 需要筛选多个表连接查询需使用视图

-- 创建视图
  drop view tb_score_csa
-- 创建视图
  create view tb_score_csa as  select a.id,a.userid,a.subject,a.score from tb_score a left JOIN tb_score b on a.userid=b.userid  where 1=1
	#查询视图
  select * from tb_score_cs	
-- 用户定义的变量 
	SET @variableOne='';
	select @variableOne :=CONCAT(@variableOne,'sum(if(subject= \'',subject,'\',score,0)) as ',subject, ',') AS aa FROM (SELECT DISTINCT subject FROM tb_score_cs) A ;
	 
	SET @variableTwo = CONCAT('select ',@variableOne,' userid from tb_score group by userid');
	-- SELECT @QQ;
	# 定义预处理语句 
	PREPARE stmt FROM @variableTwo;
	# 执行预处理语句
	EXECUTE stmt;
	# 删除(释放)定义
	-- DEALLOCATE PREPARE stmt;