Q：LISTAGG()函数用法笔记（oracle）

.LISTAGG()函数作为普通函数使用时就是查询出来的结果列转为行

☆LISTAGG 函数既是分析函数，也是聚合函数
有两种用法：
1、分析函数，如： row_number()、rank()、dense_rank() 等，用法相似
listagg(合并字段, 连接符) within group(order by 合并的字段的排序) over(partition by 分组字段)
2、聚合函数，如：sum()、count()、avg()等，用法相似
listagg(合并字段, 连接符) within group(order by 合并字段排序) --后面跟 group by 语句

实战演练

创建表

CREATE TABLE employees (
employee_id INT,
employee_name VARCHAR(50),
department VARCHAR(50)
);
INSERT INTO employees(employee_id, employee_name, department)
select 1, 'John', 'HR' from dual
union
select 2, 'Alice', 'IT' from dual
union
select 3, 'Bob', 'IT' from dual
union
select 4, 'Mary', 'HR' from dual
union
select 5, 'Eva', 'Finance' from dual

 

•  listagg（）分析函数

SELECT DEPARTMENT as 部门,
listagg(EMPLOYEE_ID,'@') within group (order by EMPLOYEE_ID) over(partition by DEPARTMENT) as 序号
from employees;

• listagg（）聚合函数

SELECT DEPARTMENT as 部门,
listagg(EMPLOYEE_ID,'@') within group (order by EMPLOYEE_ID) as 序号
from employees group by DEPARTMENT