原表:
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
*/