Oracle 列转行函数 Listagg()
来源:互联网 发布:9377皇图护盾进阶数据 编辑:程序博客网 时间:2024/05/10 13:13
这是一个Oracle的列转行函数:LISTAGG()
先看示例代码:
- with temp as(
- select 'China' nation ,'Guangzhou' city from dual union all
- select 'China' nation ,'Shanghai' city from dual union all
- select 'China' nation ,'Beijing' city from dual union all
- select 'USA' nation ,'New York' city from dual union all
- select 'USA' nation ,'Bostom' city from dual union all
- select 'Japan' nation ,'Tokyo' city from dual
- )
- select nation,listagg(city,',') within GROUP (order by city)
- from temp
- group by nation
这是最基础的用法:
LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)
用法就像聚合函数一样,通过Group by语句,把每个Group的一个字段,拼接起来。
非常方便。
同样是聚合函数,还有一个高级用法:
就是over(partition by XXX)
也就是说,在你不实用Group by语句时候,也可以使用LISTAGG函数:
- with temp as(
- select 500 population, 'China' nation ,'Guangzhou' city from dual union all
- select 1500 population, 'China' nation ,'Shanghai' city from dual union all
- select 500 population, 'China' nation ,'Beijing' city from dual union all
- select 1000 population, 'USA' nation ,'New York' city from dual union all
- select 500 population, 'USA' nation ,'Bostom' city from dual union all
- select 500 population, 'Japan' nation ,'Tokyo' city from dual
- )
- select population,
- nation,
- city,
- listagg(city,',') within GROUP (order by city) over (partition by nation) rank
- from temp
总结:LISTAGG()把它当作SUM()函数来使用就可以了。
listagg
在oracle 11g release 2 版本中新增的listagg函数,listagg是一个实现字符串聚合的oracle内建函数;
listagg(column,'分隔符') within group (order by column) over(partition by column)
分隔符可以为空,
order by必选项,可以order by null
(1)select status, listagg(risk_id, ',') within group (order by risk_id) from rp_risk group by status;
以status分组,将risk_id全部合并显示在一行
(2)与许多的聚合函数类似,listagg通过加上over()子句可以实现分析功能
select risk_id, status, listagg(risk_id, ',') within group (order by risk_id) over(partition by status) from rp_risk;
选出与当前risk_id在同一个部门的所有risk_id并合并字符串
(3)listagg聚合的结果列大小限制在varchar2类型的最大值内(比如4000);
(4)合并字符串也可以用wm_concat(column_name),所有版本的oracle都可以用这个函数
listagg()是oracle 11g release 2才有;
(5)参考链接
http://xpchild.blog.163.com/blog/static/10180985920108485721969/
- --listagg(合并多行的值为字符串,只用一列来显示)
- select status, count(*), listagg(risk_id, ',') within group (order by risk_id) from rp_risk group by status;
- select risk_id, status, listagg(risk_id, ',') within group (order by risk_id) over(partition by status) from rp_risk;
- select risk.risk_id, listagg(officer.last_name || ',' || officer.first_name, '; ') within group(order by null) from rp_risk risk, rp_risk_area_ref re, rp_risk_area area, rp_risk_officer officer
- where risk.risk_id = re.risk_id
- and re.risk_area_id = area.risk_area_id(+)
- and area.risk_officer_id = officer.risk_officer_id(+)
- group by risk.risk_id;
- --pivot(行专列,将多行的值改为多列显示)(for in的那个column,是某个列的值,也就是将某个列的值作为新的列的column,这个column下边的值好像只能来自一列)
- select * from
- (select risk.risk_id, re.risk_area_order, officer.last_name || ',' || officer.first_name fullname
- from rp_risk risk, rp_risk_area_ref re, rp_risk_area area, rp_risk_officer officer
- where risk.risk_id = re.risk_id
- and re.risk_area_id = area.risk_area_id(+)
- and area.risk_officer_id = officer.risk_officer_id(+) order by risk.risk_id desc, re.risk_area_order)
- pivot(max(fullname) for risk_area_order in (1 primaryOfficer, 2 addtionalOffcier1, 3 addtionalOffcier2)) order by risk_id desc;
- --decode(行专列,将多行的值改为多列显示)(decode的那个column,是某个列的值,也就是将某个列的值作为新的列的column,MAX聚集函数也可以用sum、min、avg等其他聚集函数替代)
- select risk_id,
- --max(decode(risk_area_order, 1, fullname)) primaryOfficer,
- --max(decode(risk_area_order, 2, fullname)) addtionalOffcier1,
- --max(decode(risk_area_order, 3, fullname)) addtionalOffcier1
- min(decode(risk_area_order, 1, fullname)) primaryOfficer,
- min(decode(risk_area_order, 2, fullname)) addtionalOffcier1,
- min(decode(risk_area_order, 3, fullname)) addtionalOffcier1
- from
- (select risk.risk_id, re.risk_area_order, officer.last_name || ',' || officer.first_name fullname from rp_risk risk, rp_risk_area_ref re, rp_risk_area area, rp_risk_officer officer
- where risk.risk_id = re.risk_id
- and re.risk_area_id = area.risk_area_id(+)
- and area.risk_officer_id = officer.risk_officer_id(+) order by risk.risk_id, re.risk_area_order)
- group by risk_id order by risk_id;
- Oracle 列转行函数 Listagg()
- Oracle 列转行函数 Listagg()
- Oracle 列转行函数 Listagg()
- Oracle 列转行函数 Listagg()
- Oracle 列转行函数 Listagg()
- Oracle 列转行函数 Listagg()
- Oracle 列转行函数 Listagg()
- Oracle 列转行函数 Listagg()
- Oracle 列转行函数 Listagg()
- Oracle 列转行函数 Listagg()
- Oracle的列转行函数:listagg()
- Oracle 列转行函数 Listagg()示例
- Oracle的列转行函数:LISTAGG()
- oracle 列转行函数 Listagg() 博客
- Oracle中的列转行函数listagg()
- oracle-listagg()函数实现列转行
- Oracle 11g 新聚集函数listagg实现列转行
- Oracle 11g 新聚集函数listagg实现列转行
- 做网站SEO收集数据的方法
- Linux下网站压力测试工具webbench下载安装使用
- IT忍者神龟之com.octo.captcha.service.CaptchaServiceException: Invalid ID, could not validate unexisting o
- jQuery select2设置默认值为下拉列表中的一个选项
- js josn prase
- Oracle 列转行函数 Listagg()
- Mybatis最入门---分页查询(拦截器分页原理及实现)
- JAVA之File文件处理
- hive表加载数据的不同方式
- 深入浅出RxJava(三)--响应式的好处
- BKDRHash
- textarea 自适应 亲测好用
- 网站“抓取异常”问题的解决方案
- Android ButterKnife Zelezny插件的安装与使用