介绍
怎么在postgresql中对string_agg函数进行合并?很多新手对此不是很清楚,为了帮助大家解决这个难题,下面小编将为大家详细讲解,有这方面需求的人可以来学习下,希望你能有所收获。
1有时候我们会需要将多条数据根据一些特别的字段做一些合并。比如下面这个查询,正常会查询出3条数据,但是我们会希望根据create_by分成两列显示
select create_by, string_agg (& # 39;, videoname & # 39;), as videonames 得到w008_video_addr_info where id 拷贝(4248、538546)
group by create_by
查询结果:
select
sa.id,
info.nickname,大敌;
,(select string_agg (v.videoname & # 39;, & # 39;),
,得到w008_school_assign_video sv
,join w008_video_addr_info v 提醒sv.videoaddrinfo =v.id
,where sv.schoolassignment=sa.id 以及v.is_removed=0,以及sv.is_removed=0,
,group by v.is_removed), as 视频,
,(select string_agg (to_char (sv.create_date, & # 39; MM-DD HH24: mi # 39;), & # 39;, & # 39;),
,得到w008_school_assign_video sv
,join w008_video_addr_info v 提醒sv.videoaddrinfo =v.id where ,,
sv.schoolassignment才能=sa.id 以及v.is_removed=0,
sv.is_removed=0,害group by v.is_removed), as viewtime
得到w008_school_assignment sa
join w008_user_business_info info 提醒sa.userlongid=info.id where sa。shchoolworkid=2514505674916356
结果:
array_agg(表达式)
把表达式变成一个数组一般配合array_to_string()函数使用
string_agg(表情,,分隔符) 直接把一个表达式变成字符串
案例:
create 表(empno 短整型,ename varchar (20), job varchar (20), mgr 短整型,,hiredate 日期,sal 长整型数字,,comm 长整型数字,,deptno 短整型);
ename insert into jinbo.employee (empno,工作,,下,,hiredate,,萨尔,,通讯,,deptno), values (7499年,& # 39;艾伦# 39;,,& # 39;业务员# 39;,,7698,,& # 39;2014 - 11 - 12 & # 39;,,16000,,300,,30);
ename insert into jinbo.employee (empno,工作,,下,,hiredate,,萨尔,,通讯,,deptno), values (7499年,& # 39;艾伦# 39;,,& # 39;业务员# 39;,,7698,,& # 39;2014 - 11 - 12 & # 39;,,16000,,300,,30);
insert into jinbo.employee (ename empno,工作,,下,,hiredate,,萨尔,,通讯,,deptno), values (7654年,& # 39;马丁# 39;,,& # 39;业务员# 39;,,7698,,& # 39;2016 - 09 - 12 & # 39;,,12000,,1400,,30);
select *,得到jinbo.employee;
|,empno ename |, job |, mgr |, hiredate |, sal |, comm |, deptno
- - - - - - - + - - - - - - - - - - - - - - - - - - - - - - + - - - - - - - - - - - - - - - - - - - - - - - - - + + - - - - - - - - - - - - - - -
,7499 |,ALLEN |, SALEMAN |, 7698, |, 2014 - 11 - 12, |, 16000, |, 300, |, 30
,7566 |,JONES |, MANAGER |, 7839, |, 2015 - 12 - 12, |, 32000, |, 0, |, 20
,7654 |,MARTIN |, SALEMAN |, 7698, |, 2016 - 09 - 12, |, 12000, |, 1400, |, 30
(3,行) 查询同一个部门下的员工且合并起来
<强>方法1:
select deptno,, string_agg (ename, & # 39; & # 39;),得到jinbo.employee group by deptno;
|,deptno string_agg
- - - - - - - - - - + - - - - - - - - - - - - - - - -
20,才能|,琼斯
30才能,|,艾伦,马丁
