LISTAGG
来源:互联网 发布:中国淘宝店主平均收入 编辑:程序博客网 时间:2024/06/06 13:59
1. 说明
对数据集排序后,再串行计算
1.1 如果是作为单行输出计算函数,会操作所有的行,然后只返回一行。
SELECT LISTAGG(last_name, '; ')
WITHIN GROUP (ORDER BY hire_date, last_name) "Emp_list",
MIN(hire_date) "Earliest"
FROM employees
WHERE department_id = 30;
Emp_list Earliest
------------------------------------------------------------ ---------
Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares 07-DEC-02
1.2 如果是操作由group by 传来的组集合,LISTAGG会把每一组作为一个计算单位,为每一组返回一行。
SELECT department_id "Dept.",
LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date) "Employees"
FROM employees
GROUP BY department_id
ORDER BY department_id;
Dept. Employees
------ ------------------------------------------------------------
10 Whalen
20 Hartstein; Fay
30 Raphaely; Khoo; Tobias; Baida; Himuro; Colmenares
40 Mavris
50 Kaufling; Ladwig; Rajs; Sarchand; Bell; Mallin; Weiss; Davie
s; Marlow; Bull; Everett; Fripp; Chung; Nayer; Dilly; Bissot
; Vollman; Stiles; Atkinson; Taylor; Seo; Fleaur; Matos; Pat
el; Walsh; Feeney; Dellinger; McCain; Vargas; Gates; Rogers;
Mikkilineni; Landry; Cabrio; Jones; Olson; OConnell; Sulliv
an; Mourgos; Gee; Perkins; Grant; Geoni; Philtanker; Markle
60 Austin; Hunold; Pataballa; Lorentz; Ernst
70 Baer
1.3 如果后面后面有分析函数时,LISTAGG会计算分区集合。
SELECT department_id "Dept", hire_date "Date", last_name "Name",
LISTAGG(last_name, '; ') WITHIN GROUP (ORDER BY hire_date, last_name)
OVER (PARTITION BY department_id) as "Emp_list"
FROM employees
WHERE hire_date < '01-SEP-2003'
ORDER BY "Dept", "Date", "Name";
Dept Date Name Emp_list
----- --------- --------------- ---------------------------------------------
30 07-DEC-02 Raphaely Raphaely; Khoo
30 18-MAY-03 Khoo Raphaely; Khoo
40 07-JUN-02 Mavris Mavris
50 01-MAY-03 Kaufling Kaufling; Ladwig
50 14-JUL-03 Ladwig Kaufling; Ladwig
70 07-JUN-02 Baer Baer
90 13-JAN-01 De Haan De Haan; King
90 17-JUN-03 King De Haan; King
100 16-AUG-02 Faviet Faviet; Greenberg
100 17-AUG-02 Greenberg Faviet; Greenberg
110 07-JUN-02 Gietz Gietz; Higgins
110 07-JUN-02 Higgins Gietz; Higgins
- LISTAGG
- listagg
- listagg
- listagg函数
- LISTAGG函数
- listagg函数
- LISTAGG 行转列
- The fuction of listagg.
- oracle中的listagg函数
- oracle 行转列 listagg wm_concat
- Oracle 之listAgg
- wm_concat与listagg
- Oracle函数之LISTAGG
- listagg小记录
- oracle函数listagg()
- 搞不懂思路listagg
- Oracle listagg 函数
- oracle listagg 用法
- ios 避免两个button同时被点击
- qt异常退出
- 链表《2》使用函数操作链表
- 2014年英语六级高频短语精选
- MyBatis的动态SQL
- LISTAGG
- VS2012版最常用的快捷键
- xcuserstate/xccheckout/pbxproj/xcworkspace提交注意事项
- jquery点击图片进行放大缩小
- ALM客户端初次登陆
- java Web 项目定时器 Spring之OpenSymphony的Quartz。
- 第九周 项目5--求一元一次方程的解
- 弹出层之2:JQuery.BlockUI
- ubuntu杂谈(三) -- 搜索软件包