Oracle数据库使用 listagg去重删除重复数据的方法汇总

create table "dept_tag_info"
(
"tag_id" bigint not null,
"tag_code" varchar(200),
"tag_name" varchar(500),
"tag_level" varchar(200),
"parent_id" bigint,
"gmt_create" datetime(6),
"create_by" varchar(50),
"update_by" varchar(50),
"gmt_modified" datetime(6),
"del_flag" char(1),
"sort" integer,
"multiple_choice" char(1),
"relation_dept_cahttp://www.chinasem.cntegory" varchar(500),
"tips_text" varchar(2000),
"remarks" varchar(500),
"enabled" char(1),
constraint "dept_tag_info_new_pk" not cluster primary key("tag_id")) storage(on "ctbiyi_data_v3", clusterbtr) ;
comment on table "dept_tag_info" is '企业标签基础信息表';
comment on column "dept_tag_info"."tag_id" is '主键';
comment on column "dept_tag_info"."tag_code" is '标签编码';
comment on column "dept_tag_info"."tag_name" is '标签名称';
comment on column "dept_tag_info"."tag_level" is '标签层级';
comment on column "dept_tag_info"."parent_id" is '父节点编码id';
comment on column "dept_tag_info"."gmt_create" is '创建时间';
comment on column "dept_tag_info"."create_by" is '创建人';
comment on column "dept_tag_info"."update_by" is '修改人';
comment on column "dept_tag_info"."gmt_modified" is '修改时间';
comment on column "dept_tag_info"."del_flag" is '删除标记 0-未删除 1-已删除';
comment on column "dept_tag_info"."sort" is '排序';
comment on column "dept_tag_info"."multiple_choice" is '多选（1是 0否）';
comment on column "dept_tag_info"."relation_dept_category" is '关联主体';

    select 
    t.tag_code,
    t.tag_name,
    listagg(t.tag_level, ',') within group(order by t.tag_level) as tag_levels
from 
    dept_tag_info t
group by 
    t.tag_code, 
    t.tag_name;

select 
    t.tag_code,
    t.tag_name,
    wm_concat(distinct t.tag_level) as tag_levels
from 
    dept_tag_info t
group by 
    t.tag_code, 
    t.tag_name;

select 
    t.tag_code,
    t.tag_name,
    listagg(t.tag_level, ',') within group(order by t.tag_level) as tag_levels
from 
    (select distinct s.tag_code, s.tag_name, s.tag_level
     from dept_tag_info s) t
group by 
    t.tag_code, 
    t.tag_name;

select 
    t.tag_code,
    t.tag_name,
    rtrim(
        xmlagg(
            xmlparse(content t.tag_level || ',' wellformed)
            order by t.tag_level
        ).getclobval(), 
        ','
    ) as tag_levels
from 
    (select distinct s.tag_code, s.tag_name, s.tag_level
     from dept_tag_info s) t
group by 
    t.tag_code, 
    t.tag_name;

-- 查找重复记录
select "tag_id", count(*) as cnt
from dept_tag_info
group by "tag_id"
having count(*) > 1
order by cnt desc;

delete from dept_tag_info t
where t.rowid in (
    select rid
    from (
        select t1.rowid as rid, row_number() over (partition by t1.tag_code, t1.tag_name order by 1) as rn
        from dept_tag_info t1
    ) t2
    where t2.rn > 1
);

SELECT   
    RTRIM(XMLAGG(XMLPARSE(content = dd.product_factory || ',' wellformed)   
    ORDER BY dd.product_factory).getclobval(), ',') AS productFactory  
FROM   
    product_details dd;

SELECT   
    LISTAGG(dd.change_after_part_name, ',') WITHIN GROUP (ORDER BY dd.change_after_part_name) AS productName  
FROM   
    product_changes dd  
WHERE   
    dd.change_date > '2023-01-01';

SELECT   
    RTRIM(XMLAGG(XMLPARSE(content = dd.product_factory || ',' wellformed)  
    ORDER BY dd.product_factory).getclobval(), ',') AS productFactory,  
    LISTAGG(cc.change_after_part_name, ',') WITHIN GROUP (ORDER BY cc.change_after_part_name) AS productName  
FROM   
    product_details dd  
LEFT JOIN   
    product_changes cc ON 编程dd.product_id = cc.product_id  
WHERE   
    cc.change_date > '2023-01-01';