|
|
这是我正在尝试做的事情和最简单的工作例子：
4 [! M9 b1 E" e我有一个查询，如下所示：1 Q2 @' S) J5 X
/*with tran_party as -- ALL DUMMY DATA ARE IN THESE CTE FOR YOUR REFERENCE (select 1 tran_party_id,11 transaction_id,101 team_id_redirect from dual union all select 2,11,101 from dual union all select 3,11,102 from dual union all select 4,12,103 from dual union all select 5,12,103 from dual union all select 6,12,104 from dual union all select 7,13,104 from dual union all select 8,13,105 from dual), tran as (select 11 transaction_id,1001 account_id,1034.93 amount from dual union all select 12,1001,2321.89 from dual union all select 13,1002,3201.47 from dual), account as (select 1001 account_id,111 team_id from dual union all select 1002,112 from dual), team as (select 101 team_id,'UUU' as team_code from dual union all select 102,'VV' from dual union all select 103,'WWW' from dual union all select 104,'XXXXX' from dual union all select 105,'Z' from dual)-- */-- The Actual Queryselect a.account_id, t.transaction_id, (select listagg (tm_redir.team_code,within group (order by tm_redir.team_code) from tran_party tp_redir inner join team tm_redir on tp_redir.team_id_redirect = tm_redir.team_id inner join tran t_redir on tp_redir.transaction_id = t_redir.transaction_id where t_redir.account_id = a.account_id and t_redir.transaction_id != t.transaction_id) as teams_redirected from tran t inner join account a on t.account_id = a.account_id;注意： tran_party.team_id_redirect 是引用 team.team_id 的外键。# Z5 \# d# s8 C& {1 \6 \
电流输出：
/ \- z, P1 X) iACCOUNT_ID TRANSACTION_ID TEAMS_REDIRECTED---------- -------------- ----------------1001 11 WWW,WWW,XXXXX1001 12 100011001111212 UUU,UUU,VV1002 133预期产量：& Q+ Q6 J4 ?/ Y% V! O ~
我希望TEAMS_REDIRECTED列中的重复项只选择一次，如下所示：* U2 V2 o p: \6 U0 ?6 v
ACCOUNT_ID TRANSACTION_ID TEAMS_REDIRECTED---------- -------------- ----------------1001 1111 11111111111111111WWW,XXXXX1001 12 100011001111212 UUU,VV1002 133我试过的
8 N% z0 U' t. Y, ] C5 o! c5 v, Dtran_party我写了一个内联视图，而不是直接从中选择，而是从中选择tran_party如下所示：
6 I% U2 U5 J& _! q' Nselect a.account_id, t.transaction_id, (select listagg (tm_redir.team_code,within group (order by tm_redir.team_code) from (select distinct transaction_id,team_id_redirect -- Note this inline view from tran_party) tp_redir inner join team tm_redir on tp_redir.team_id_redirect = tm_redir.team_id inner join tran t_redir on tp_redir.transaction_id = t_redir.transaction_id where t_redir.account_id = a.account_id and t_redir.transaction_id != t.transaction_id) as teams_redirected from tran t inner join account a on t.account_id = a.account_id;虽然这确实给了我预期的输出，但当我在实际代码中使用这个解决方案时，检索一行大约需要13秒。因此，我不能使用我尝试过的东西。- y- q8 g4 ~7 d9 f- b# m
感谢任何帮助。
# ^& X' E, U# e2 S) c& W) D
" g K2 v$ r o 解决方案: D# H* u" ^+ h8 z/ j2 a5 q
以下方法用于摆脱嵌入式视图以获取重复项。REGEXP_REPLACE和RTRIM在LISTAGG在函数上获的不同结果集aggregatedlist。因此，它不会多次扫描。' W9 x/ e/ F$ M: Y3 e1 G
将此片段添加到您的代码中，& G! W+ U, J. f& q& N
RTRIM(REGEXP_REPLACE(listagg (tm_redir.team_code,WITHIN GROUP (ORDER BY tm_redir.team_code), ([^,] )(,\1) ','\1'), ,')修改后的查询-/ }( G- W8 ]6 }- Y" ?5 F% C! n
SQL> with tran_party as -- ALL DUMMY DATA ARE IN THESE CTE FOR YOUR REFERENCE 2 (select 1 tran_party_id,11 transaction_id,101 team_id_redirect 3 from dual union all 55 select 2,11,101 from dual union all select 3,11,102 from dual 8 union all select 4,12,103 from dual union all 1111 select 5,12,103 from dual 122 union all select 6,12,104 from dual union all select 7,13,104 from dual union all select 8,13,105 from dual),18 tran as (select 11 transaction_id,1001 account_id,1034.93 amount from dual union all 21 select 12,1001,2321.89 from dual 2222 union all 233 select 13,1002,3201.47 from dual),24 account as 255 (select 1001 account_id,111 team_id from dual union all select 1002,112 from dual), team as (select 101 team_id,'UUU' as team_code from dual3030 union all 31 select 102,'VV' from dual 322 union all333333 select 103,'WWW' from dual union all select 104,'XXXXX' from dual union all select 105,'Z' from dual) 38 -- The Actual Query 39 select a.account_id,40 40 t.transaction_id,411 (SELECT RTRIM( 42 REGEXP_REPLACE(listagg (tm_redir.team_code, WITHIN GROUP (ORDER BY tm_redir.team_code),4444 ([^,] )(,\1) ','\1、45 ''，46 from tran_party tp_redir inner join team tm_redir 48 on tp_redir.team_id_redirect = tm_redir.team_id inner join tran t_redir 5050 on tp_redir.transaction_id = t_redir.transaction_id where t_redir.account_id = a.account_id 522 and t_redir.transaction_id != t.transaction_id) 533 AS teams_redirected 54 from tran t inner join account a on t.account_id = a.account_id 55 /ACCOUNT_ID TRANSACTION_ID TEAMS_REDIRECTED---------- -------------- -------------------- 111111WWW,XXXXX 1001 1212 1001 UUU,VV 1002 133133SQL> |
|
技术问答
60 人阅读
|
0 人回复
|
2023-09-13
