在Oracle中如何清楚地列出2列级别[重复]

kcrjzv8t 于 2023-08-04 发布在 Oracle

关注(0)|答案(1)|浏览(82)

此问题在此处已有答案：

LISTAGG in Oracle to return distinct values（24回答）
22天前关闭
我有一个表格，数据如下：

emp_id  company_id  company_version_id  account_id  duplicate_account_id
174043265   10622557    1   201013278   101104723
174043265   10622557    1   201013278   200999888
174043265   10622557    1   201013278   203010306
174043265   10622557    1   201013278   205436979
174043265   10622557    1   201013278   205436980
174043265   10622557    1   201013293   101104723
174043265   10622557    1   201013293   200999888
174043265   10622557    1   201013293   203010306
174043265   10622557    1   201013293   205436979
174043265   10622557    1   201013293   205436980

字符串
在Oracle中，如何实现以下功能：

emp_id  company_id  company_version_id  account_id  duplicate_account_id
174043265   10622557    1   201013278, 201013293    101104723, 200999888, 203010306, 205436979, 205436980

型
x1c 0d1x的数据
创建表并测试的代码：

create table dummy_data AS
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013278 account_id,  101104723 duplicate_account_id from dual union all
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013278 account_id,  200999888 duplicate_account_id from dual union all
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013278 account_id,  203010306 duplicate_account_id from dual union all
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013278 account_id,  205436979 duplicate_account_id from dual union all
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013278 account_id,  205436980 duplicate_account_id from dual union all
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013293 account_id,  101104723 duplicate_account_id from dual union all
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013293 account_id,  200999888 duplicate_account_id from dual union all
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013293 account_id,  203010306 duplicate_account_id from dual union all
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013293 account_id,  205436979 duplicate_account_id from dual union all
select 174043265 emp_id,  10622557 company_id,  1 company_version_id, 201013293 account_id,  205436980 duplicate_account_id from dual

型
如果listagg长度限制也被处理（没有截断），那就太好了。

谢谢-谢谢

oracle

来源：https://stackoverflow.com/questions/76667341/in-oracle-how-to-distinctly-listagg-on-2-column-level

1条答案

按热度按时间

1mrurvl11#

说你使用Oracle是不够的-它是哪个版本？因为，listagg在最近的版本中支持distinct：

SQL> select emp_id, company_id, company_version_id,
  2    listagg(distinct account_id, ', ') within group (order by account_id) account_id,
  3    listagg(distinct duplicate_account_id, ', ') within group (order by duplicate_account_id) duplicate_account_id
  4  from dummy_data
  5  group by emp_id, company_id, company_version_id;

    EMP_ID COMPANY_ID COMPANY_VERSION_ID ACCOUNT_ID                     DUPLICATE_ACCOUNT_ID
---------- ---------- ------------------ ------------------------------ ------------------------------------------------------------
 174043265   10622557                  1 201013278, 201013293           101104723, 200999888, 203010306, 205436979, 205436980

SQL>

字符串
如果你的数据库不支持它，首先找到不同的值，然后聚合它们：

SQL> with
  2  temp_acc as
  3    (select distinct emp_id, company_id, company_version_id, account_id
  4     from dummy_data
  5    ),
  6  temp_dup_acc as
  7    (select distinct emp_id, company_id, company_version_id, duplicate_account_id
  8     from dummy_data
  9    ),
 10  agg_acc as
 11    (select a.emp_id, a.company_id, a.company_version_id,
 12       listagg(a.account_id, ', ') within group (order by a.account_id) account_id
 13     from temp_acc a
 14     group by a.emp_id, a.company_id, a.company_version_id
 15    ),
 16  agg_dup as
 17    (select a.emp_id, a.company_id, a.company_version_id,
 18       listagg(a.duplicate_account_id, ', ') within group (order by a.duplicate_account_id) duplicate_account_id
 19     from temp_dup_acc a
 20     group by a.emp_id, a.company_id, a.company_version_id
 21    )
 22  select a.emp_id, a.company_id, a.company_version_id, a.account_id, d.duplicate_account_id
 23  from agg_acc a join agg_dup d on a.emp_id = d.emp_id
 24                                     and a.company_id = d.company_id
 25                                     and a.company_version_id = d.company_version_id;

    EMP_ID COMPANY_ID COMPANY_VERSION_ID ACCOUNT_ID                     DUPLICATE_ACCOUNT_ID
---------- ---------- ------------------ ------------------------------ ------------------------------------------------------------
 174043265   10622557                  1 201013278, 201013293           101104723, 200999888, 203010306, 205436979, 205436980

SQL>

型

赞(0）回复(0）举报 2023-08-04

我来回答

在Oracle中如何清楚地列出2列级别[重复]

1条答案

相关问题

热门标签

最新问答