mysql行列转置

• 假设有下面一张表，每行记录了每位学生各科的成绩

mysql> select * from users;
+----------+--------+-------+
| name     | course | grade |
+----------+--------+-------+
| zhangsan | Java   |    70 |
| zhangsan | C++    |    80 |
| lisi     | java   |    90 |
| lisi     | C#     |    60 |
+----------+--------+-------+

• 需求：假设要 以学生的维度，汇总各科的成绩（每人一行），如下所示：

+----------+------+------+------+
| name     | java | C++  | C#   |
+----------+------+------+------+
| lisi     |   90 | NULL |   60 |
| zhangsan |   70 |   80 | NULL |
+----------+------+------+------+

• 附：准备测试数据

CREATE DATABASE IF NOT EXISTS bak;
use bak;
drop table if exists users;
create table users(name nvarchar(10), course nvarchar(10), grade int);
insert into users values('zhangsan','Java',70);
insert into users values('zhangsan','C++',80);
insert into users values('lisi','java',90);
insert into users values('lisi','C#',60);

• 方法1：利用 case when 实现行列转置

select name,
  sum(case when course='java' then grade end) as 'java',
  sum(case when course='C++' then grade end) as 'C++',
  sum(case when course='C#' then grade end) as 'C#'
from users 
group by name order by name;

• 方法2：

select distinct c.name AS name,
  (select grade from users where name = c.name and course = 'java' ) as 'java',
  (select grade from users where name = c.name and course = 'C++' ) as 'C++',
  (select grade from users where name = c.name and course = 'C#' ) as 'C#'
from users c order by name;