MySQL 行转列 列转行

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);

SELECT * FROM tb_score;

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;

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;

SELECT IFNULL(userid,‘total‘) AS 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 政治,
  SUM(IF(`subject`=‘total‘,score,0)) AS total
FROM(
    SELECT userid,IFNULL(`subject`,‘total‘) AS `subject`,SUM(score) AS score
    FROM tb_score
    GROUP BY userid,`subject`
    WITH ROLLUP
    HAVING userid IS NOT NULL
)AS A 
GROUP BY userid
WITH ROLLUP;

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 政治,
  SUM(score) AS TOTAL 
FROM tb_score 
GROUP BY userid 
UNION
SELECT 
  ‘TOTAL‘,
  SUM(IF(`subject` = ‘语文‘, score, 0)) AS 语文,
  SUM(IF(`subject` = ‘数学‘, score, 0)) AS 数学,
  SUM(IF(`subject` = ‘英语‘, score, 0)) AS 英语,
  SUM(IF(`subject` = ‘政治‘, score, 0)) AS 政治,
  SUM(score) 
FROM tb_score 

SELECT 
  IFNULL(userid, ‘TOTAL‘) AS 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 政治,
  SUM(score) AS TOTAL 
FROM tb_score 
GROUP BY userid WITH ROLLUP ;

SELECT 
  userid,
  GROUP_CONCAT(`subject`, ":", score) AS 成绩 
FROM
  tb_score 
GROUP BY userid 

CREATE TABLE tb_score1(
    id INT(11) NOT NULL auto_increment,
    userid VARCHAR(20) NOT NULL COMMENT ‘用户id‘,
    cn_score DOUBLE COMMENT ‘语文成绩‘,
    math_score DOUBLE COMMENT ‘数学成绩‘,
    en_score DOUBLE COMMENT ‘英语成绩‘,
    po_score DOUBLE COMMENT ‘政治成绩‘,
    PRIMARY KEY(id)
)ENGINE = INNODB DEFAULT CHARSET = utf8;

INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES (‘001‘,90,92,80,0);
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES (‘002‘,88,90,75.5,0);
INSERT INTO tb_score1(userid,cn_score,math_score,en_score,po_score) VALUES (‘003‘,70,85,90,82);

SELECT * FROM tb_score1;

SELECT userid,‘语文‘ AS course,cn_score AS score FROM tb_score1
UNION ALL
SELECT userid,‘数学‘ AS course,math_score AS score FROM tb_score1
UNION ALL
SELECT userid,‘英语‘ AS course,en_score AS score FROM tb_score1
UNION ALL
SELECT userid,‘政治‘ AS course,po_score AS score FROM tb_score1
ORDER BY userid