oracle数据库数据合并,oracle数据库将多条记录合并到一条中显示

场景将账户表中同一个用户的账户号合并显示在一条记录中

select name, listagg(account_name, ',') within group(order by name) from user_account group by name;

效果图

数据准备

create table user_account(

id number(12,0) primary key,

name varchar(50),

account_name varchar(50),

balance number(10,2)

);

insert into user_account values(1, 'wangwu', 'wangwuAccount1', 600);

insert into user_account values(2, 'wangwu', 'wangwuAccount2', 400);

insert into user_account values(3, 'lisi', 'lisiAccount1', 200);

insert into user_account values(4, 'lisi', 'lisiAccount2', 1000);

insert into user_account values(5, 'zhangsan', 'zhangsanAccount1', 500);

insert into user_account values(6, 'zhangsan', 'zhangsanAccount2', 500);

insert into user_account values(7, 'zhangsan', 'zhangsanAccount3', 500);

select * from user_account order by id

SQL语句

select name, listagg(account_name, ',') within group(order by name) from user_account group by name;