如何在 Oracle 中执行字符串聚合/连接?
问题描述:
您想在 Oracle 中将字符串连接为逗号分隔的文本。
解决方案:
Oracle 提供几种执行字符串聚合的方法。您在互联网上最常看到的用法是将多行转换为带分隔符的单行。
从 Oracle 11.2 版本开始，添加了一个名为 listagg 的新内置函数，用于执行字符串聚合。listagg 函数使用两个参数:第一个是我们要聚合的字符串列或表达式;第二个是可选参数，它是聚合结果中字符串之间的分隔符。 within group 是必需的，并且需要我们指定 order by 子句，该子句告诉 Oracle 字符串应按什么顺序进行聚合。
为了演示字符串聚合的用法，我们将使用学生表来查找 FI_ACCOUNT 职位 ID 下的资源。
示例
SELECT class_id, first_name || ' ' || last_name FROM students WHERE class_id = 'INS_STAFF' ;
执行后，该职位下有 5 个资源。我们将使用 listagg 函数将这 5 个资源从多行转换为单行，并以逗号作为分隔符。
INS_STAFF BROWN MICHAEL INS_STAFF JONES WILLIAM INS_STAFF MILLER DAVID INS_STAFF DAVIS RICHARD INS_STAFF GARCIA CHARLES
SQL:字符串聚合
SELECT class_id, LISTAGG(first_name || ' ' || last_name , ', ' ) WITHIN GROUP (ORDER BY first_name) FROM students WHERE class_id = 'INS_STAFF' GROUP BY class_id ;
INS_STAFF BROWN MICHAEL, DAVIS RICHARD, GARCIA CHARLES, JONES WILLIAM, MILLER DAVID
我们可以进一步将此操作应用于整个表的所有 Job ID。
示例
SELECT class_id, LISTAGG(first_name || ' ' || last_name , ', ' ) WITHIN GROUP (ORDER BY first_name) FROM students GROUP BY class_id ;
输出:SQL 中的几行
CL_MATH ANDERSON DANIEL, MARTINEZ CHRISTOPHER, TAYLOR PAUL, THOMAS MARK, WILSON THOMAS CL_PHY RODRIGUEZ JOSEPH INS_CHAIRMAN SMITH JAMES INS_STAFF BROWN MICHAEL, DAVIS RICHARD, GARCIA CHARLES, JONES WILLIAM, MILLER DAVID INS_VP JOHNSON JOHN, WILLIAMS ROBERT
listagg 函数易于使用，并且作为内置函数性能卓越。但它也有一些缺点:
- 它无法返回大于 4,000 或 32,767 字节的字符串。
- 在 Oracle 19c 版本之前，它无法执行 distinct 聚合。
- 在 Oracle 11.2 之前的版本中不存在此功能。
数据无法容纳在 4,000 字节以内的最常见情况之一是数据不唯一。如果您的数据库是 19c 或更高版本，您可以执行 distinct 字符串聚合，从而尽可能减少 varchar2 类型的匹配次数。
SQL:使用 Distinct 子句进行字符串聚合
SELECT class_id, LISTAGG( DISTINCT first_name || ' ' || last_name , ', ' ) WITHIN GROUP (ORDER BY first_name) FROM students GROUP BY class_id ;
输出
CL_MATH ANDERSON DANIEL, MARTINEZ CHRISTOPHER, TAYLOR PAUL, THOMAS MARK, WILSON THOMAS CL_PHY RODRIGUEZ JOSEPH INS_CHAIRMAN SMITH JAMES INS_STAFF BROWN MICHAEL, DAVIS RICHARD, GARCIA CHARLES, JONES WILLIAM, MILLER DAVID INS_VP JOHNSON JOHN, WILLIAMS ROBERT
数据准备:用于解决该问题的数据如下所示。
示例
DROP TABLE students; COMMIT; CREATE TABLE students ( student_id NUMBER(6) , first_name VARCHAR2(20) , last_name VARCHAR2(25) , email VARCHAR2(40) , phone_number VARCHAR2(20) , join_date DATE , class_id VARCHAR2(20) , fees NUMBER(8,2) , professor_id NUMBER(6) , department_id NUMBER(4) ) ;
示例
CREATE UNIQUE INDEX stu_id_pk ON students (student_id) ;
INSERT INTO students VALUES (100,'SMITH','JAMES','SMITH.JAMES@notreal.com','111.111.1245',TO_DATE('17-06-2003','DD-MM-YYYY'),'INS_CHAIRMAN',24000,NULL,NULL);
INSERT INTO students VALUES (101,'JOHNSON','JOHN','JOHNSON.JOHN@notreal.com','111.111.1246',TO_DATE('21-09-2005','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (102,'WILLIAMS','ROBERT','WILLIAMS.ROBERT@notreal.com','111.111.1247',TO_DATE('13-01-2001','DD-MM-YYYY'),'INS_VP',17000,100,90);
INSERT INTO students VALUES (103,'BROWN','MICHAEL','BROWN.MICHAEL@notreal.com','111.111.1248',TO_DATE('03-01-2006','DD-MM-YYYY'),'INS_STAFF',9000,102,60);
INSERT INTO students VALUES (104,'JONES','WILLIAM','JONES.WILLIAM@notreal.com','111.111.1249',TO_DATE('21-05-2007','DD-MM-YYYY'),'INS_STAFF',6000,103,60);
INSERT INTO students VALUES (105,'MILLER','DAVID','MILLER.DAVID@notreal.com','111.111.1250',TO_DATE('25-06-2005','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (106,'DAVIS','RICHARD','DAVIS.RICHARD@notreal.com','111.111.1251',TO_DATE('05-02-2006','DD-MM-YYYY'),'INS_STAFF',4800,103,60);
INSERT INTO students VALUES (107,'GARCIA','CHARLES','GARCIA.CHARLES@notreal.com','111.111.1252',TO_DATE('07-02-2007','DD-MM-YYYY'),'INS_STAFF',4200,103,60);
INSERT INTO students VALUES (108,'RODRIGUEZ','JOSEPH','RODRIGUEZ.JOSEPH@notreal.com','111.111.1253',TO_DATE('17-08-2002','DD-MM-YYYY'),'CL_PHY',12008,101,100);
INSERT INTO students VALUES (109,'WILSON','THOMAS','WILSON.THOMAS@notreal.com','111.111.1254',TO_DATE('16-08-2002','DD-MM-YYYY'),'CL_MATH',9000,108,100);
INSERT INTO students VALUES (110,'MARTINEZ','CHRISTOPHER','MARTINEZ.CHRISTOPHER@notreal.com','111.111.1255',TO_DATE('28-09-2005','DD-MM-YYYY'),'CL_MATH',8200,108,100);
INSERT INTO students VALUES (111,'ANDERSON','DANIEL','ANDERSON.DANIEL@notreal.com','111.111.1256',TO_DATE('30-09-2005','DD-MM-YYYY'),'CL_MATH',7700,108,100);
INSERT INTO students VALUES (112,'TAYLOR','PAUL','TAYLOR.PAUL@notreal.com','111.111.1257',TO_DATE('07-03-2006','DD-MM-YYYY'),'CL_MATH',7800,108,100);
INSERT INTO students VALUES (113,'THOMAS','MARK','THOMAS.MARK@notreal.com','111.111.1258',TO_DATE('07-12-2007','DD-MM-YYYY'),'CL_MATH',6900,108,100);
COMMIT;
