mysql - case-when统计报表

CREATE TABLE `tb` (
      `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `name` varchar(10) DEFAULT NULL,
      `subject` varchar(10) DEFAULT NULL,
      `score` int(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
) ENGINE=InnoDB;

INSERT INTO `tb` (`id`, `name`, `subject`, `score`) VALUES ('1', '张三', '语文', '74');
INSERT INTO `tb` (`id`, `name`, `subject`, `score`) VALUES ('2', '张三', '数学', '83');
INSERT INTO `tb` (`id`, `name`, `subject`, `score`) VALUES ('3', '张三', '物理', '93');
INSERT INTO `tb` (`id`, `name`, `subject`, `score`) VALUES ('4', '李四', '语文', '74');
INSERT INTO `tb` (`id`, `name`, `subject`, `score`) VALUES ('5', '李四', '数学', '84');
INSERT INTO `tb` (`id`, `name`, `subject`, `score`) VALUES ('6', '李四', '物理', '94');

如果已经知道数据分布情况，就很好处理

SELECT
    T.name 姓名,
    max(CASE T.subject WHEN '语文' THEN T.score ELSE 0 END) 语文,
    max(CASE T.subject WHEN '数学' THEN T.score ELSE 0 END) 数学,
    max(CASE T.subject WHEN '物理' THEN T.score ELSE 0 END) 物理
FROM tb T
GROUP BY T.name;

如果不确定有多少类型的数据，可以使用动态 SQL 的方式实现

SET @str='';
SELECT group_concat(
    DISTINCT(
        CONCAT('max(CASE T.subject WHEN \'', A.subject, '\' THEN T.score ELSE 0 end) ',A.subject)
    )
) INTO @str FROM tb A ;
SET @str=CONCAT('SELECT T.name 姓名,', @str, ' FROM tb T GROUP BY T.name');

-- 准备，执行
PREPARE stmt FROM @str;  
EXECUTE stmt;  

-- 释放语句
DEALLOCATE PREPARE stmt;