引言
相信大家都遇到过SQL中的横竖转换问题。比如说有个学生表跟专业课表,
<学生>
姓名 | 年龄 |
---|---|
学生1 | 19 |
学生2 | 20 |
<专业课>
课程名 | 选修学生 | 任课教师 |
---|---|---|
语文 | 学生1 | 语文教师 |
数学 | 学生1 | 数学教师 |
语文 | 学生2 | 语文教师 |
化学 | 学生2 | 化学教师 |
如果需要找到每个学生分别选修了什么课程,理所当然要把两个表结合,select的结果是下面这样的。这样的结果不是我们想要的,因为学生姓名是冗余的。
普通select结合结果
姓名 课程名 任课教师
学生1 语文 语文教师
学生1 数学 数学教师
学生2 语文 语文教师
学生2 化学 化学教师
但通常我们想要的是下面这样的。也就是把学生1的选课信息group by一下,完成一个横竖转化。而且我们可能会把结果返回给一个java实体类,所以最好是Json形式。这样我们就能把课程信息通过Json封装成一个实体类。
期待结果
姓名 课程信息
学生1 [{课程名:语文,课程教师:语文教师},{课程名:数学,课程教师:数学教师}]
学生2 [{课程名:语文,课程教师:语文教师},{课程名:化学,课程教师:化学教师}]
这个实体类可能是下面这样,Result实体类又包含了Course类,如果是上述的“普通select结合结果”,那么我们就必须在java中自己组合,非常麻烦。要得到上述的“期待结果”,则需要使用json函数了。
Result.java
// 学生姓名
private String studentName;
// 选课信息
private Course course;
Course.java
// 课程名
private String courseName;
// 课程教师
private String courseTeacher;
使用json_agg跟json_build_object
先看结论。SQL如下。结果便是上述的期待结果。
select
t1.学生姓名
, json_agg(
json_build_object('courseName',t2.courseName,'courseTeacher',t2.courseTeacher)
)
from
学生表 t1
left join
专业课表 t2
on t1.学生姓名 = t2.学生姓名
group by
t1.学生姓名
什么是json_agg
json_agg是一个聚合函数,通常跟group by配合使用,将值聚集成一个 JSON 数组。
函数名 | 参数 | 返回值 |
---|---|---|
json_agg(expression) | any | json |
json_agg支持排序,比如上面的sql可以按照课程名排序order by t2.courseName
select
t1.学生姓名
, json_agg(
json_build_object('courseName',t2.courseName,'courseTeacher',t2.courseTeacher)
order by t2.courseName --支持排序
)
from
学生表 t1
left join
专业课表 t2
on t1.学生姓名 = t2.学生姓名
group by
t1.学生姓名
什么是json_object_agg
json_object_agg是将名字/值对聚集成一个 JSON 对象的函数。
函数名 | 参数 | 返回值 |
---|---|---|
json_object_agg(name, value) | (any, any) | json |
结论
如果不实现横竖转换,可能需要java中手动组装SQL结果进行聚合操作,或者二次执行sql。使用json_agg跟json_build_object函数可以将结果封装成json,直接放进实体类。