1 有时候我们会需要将多条数据根据一些特别的字段做一些合并。比如下面这个查询,正常会查询出3条数据,但是我们会希望根据create_by 分成两列显示
2 这时候需要用到string_agg函数,先通过group by分组,在进行合并,当然查询结果需要满足group by的限制;sql语句:
select create_by,string_agg(videoname,',') as videonames from w008_video_addr_info where id in (4248,538,546)
group by create_by
查询结果:
3 复杂一些的应用场景(子查询):
下面的语句是我用来查询一个学生在什么时间看了哪些视频:
select
sa.id,
info.nickname,
(select string_agg(v.videoname,',')
from w008_school_assign_video sv
join w008_video_addr_info v on sv.videoaddrinfo =v.id
where sv.schoolassignment=sa.id and v.is_removed=0 and sv.is_removed=0
group by v.is_removed) as videos,
(select string_agg(to_char(sv.create_date, 'MM-DD HH24:MI'),',')
from w008_school_assign_video sv
join w008_video_addr_info v on sv.videoaddrinfo =v.id where
sv.schoolassignment=sa.id and v.is_removed=0
and sv.is_removed=0 group by v.is_removed) as viewtime
from w008_school_assignment sa
join w008_user_business_info info on sa.userlongid=info.id where sa.shchoolworkid=2514505674916356
结果:
当然,string_agg(field,'分隔符');分隔符可以填写其他任意的字符,方便后期处理即可;