MySql列转行
用户表
年级
班级
成绩表
科目表
预期实体类
public class ChengJiDan {
/**
* 学生ID
*/
private int id;
/**
* 姓名
*/
private String username;
/**
* 年级
*/
private String year;
/**
* 班级
*/
private String classs;
/**
* 语文
*/
private int yuwen;
/**
* 数学
*/
private int shuxue;
/**
* 总分
*/
private int totle;
/**
* 排名
*/
private int rank;
}
基础查询结果
select
us.id,
us.username,
ye.name year,
ye.id yid,
cl.className classs,
cl.id cid,
su.subjectName,
sc.sco
from user as us
left join year as ye on us.year = ye.id
left join class as cl on us.class = cl.id
left join score as sc on us.id = sc.userId
left join subject as su on sc.subjectId = su.id
涉及列转行问题
改造后
select
id,
username,
year,
classs,
yuwen,
shuxue,
yid,
cid,
(yuwen + shuxue) totle
from
(select
us.id,
us.username,
ye.name year,
ye.id yid,
cl.className classs,
cl.id cid,
Max(case su.subjectName when '语文' then sc.sco else 0 end ) yuwen,
Max(case su.subjectName when '数学' then sc.sco else 0 end ) shuxue
from user as us
left join year as ye on us.year = ye.id
left join class as cl on us.class = cl.id
left join score as sc on us.id = sc.userId
left join subject as su on sc.subjectId = su.id
group by us.id,us.username,ye.name,cl.className
) as b order by yid ,cid ,totle desc
效果