Mysql case when 详细介绍

当满足某一条件时，执行某一result

case  
    when condition then result
    when condition then result
    when condition then result
else result
end

建表

CREATE TABLE `cj` (
  `name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `subject` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `result` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

把上表以这样的结果输出

使用case when 实现

select name,
sum(case subject when '语文' then result else 0 end) as '语文',
sum(case subject when '数学' then result else 0 end) as '数学',
sum(case subject when '物理' then result else 0 end) as '物理'
from cj
group by name