SQL行列转化案例(成绩表+学籍表)

原表：

1.创表和插入数据：

-- 建表语句
create table 成绩表 (
学号 varchar(12),
科目 varchar(11),
成绩 int
);

insert into 成绩表  values('001','语文','80');
insert into 成绩表  values('002','语文','66');
insert into 成绩表 values('001','数学','60');
insert into 成绩表  values('002','数学','72');

create table 学籍表 (
学号 varchar(12),
姓名 varchar(12),
班级 varchar(12)
);

insert into  学籍表 values('001','张三','01');
insert into  学籍表 values('002','李四','02');

2.代码实现：

-- 方法1： 
SELECT
c.*,(语文+数学) AS 总分
FROM
(
SELECT a.学号,a.姓名,b.语文,b.数学
FROM 学籍表 a 
INNER JOIN
(select 学号,
max(case 科目 when'语文' then 成绩 else 0 end ) as '语文',
max(case 科目 when'数学' then 成绩 else 0 end) as'数学',
SUM(成绩) 总分
from 成绩表 group by 学号 )b  
ON a.学号 = b.学号) c

-- 方法2:

SELECT a.学号,姓名,语文,数学,总分 FROM 学籍表 b
left join 
(select
成绩表.学号,
max(case when 科目='语文' then 成绩 end) as 语文,
max(case when 科目='数学' then 成绩 end) as 数学,
sum(成绩) as 总分
from 成绩表
group by 成绩表.学号) a
on a.学号 = b.学号

-- ERROR AISHUJUXUEYUAN为错解
select
学籍表.学号,
姓名,
max(case when 科目='语文' then 成绩 end) as 语文,
max(case when 科目='数学' then 成绩 end) as 数学,
sum(成绩) as 总分
from 成绩表
left join 学籍表
on 成绩表.学号 = 学籍表.学号
group by 成绩表.学号;
/*
1055 - Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'test.csv.学籍表.姓名' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
*/