MYSQL行转列-动态修改

比眉伴天荒 2022-12-24 04:56 158阅读 0赞

建表语句:

CREATE TABLE `tb_score` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `userid` varchar(20) NOT NULL COMMENT '用户id',
    `subject` varchar(20) DEFAULT NULL COMMENT '科目',
    `score` double DEFAULT NULL COMMENT '成绩',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
    
    INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (1, '001', '语文', 90);
    INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (2, '001', '数学', 92);
    INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (3, '001', '英语', 80);
    INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (4, '002', '语文', 88);
    INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (5, '002', '数学', 90);
    INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (6, '002', '英语', 75.5);
    INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (7, '003', '语文', 70);
    INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (8, '003', '数学', 85);
    INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (9, '003', '英语', 90);
    INSERT INTO `tb_score`(`id`, `userid`, `subject`, `score`) VALUES (10, '003', '政治', 85);

# 1、使用case...when....then 进行行转列 #

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

# 2. 使用IF() 进行行转列： #

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、利用SUM(IF()) 生成列 + WITH ROLLUP 生成汇总行,并利用 IFNULL将汇总行标题显示为Total #

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;

4.动态，适用于列不确定情况

SET @EE='';
    select @EE :=CONCAT(@EE,'sum(if(subject= \'',subject,'\',score,0)) as ',subject, ',') AS aa FROM (SELECT DISTINCT subject FROM tb_score) A ;
    
    SET @QQ = CONCAT('select ifnull(userid,\'TOTAL\')as userid,',@EE,' sum(score) as TOTAL from tb_score group by userid WITH ROLLUP');
    -- SELECT @QQ;
    
    PREPARE stmt FROM @QQ;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

#  #