使用LISTAGG时需要正确的逻辑
使用LISTAGG时需要正确的逻辑
提问于 2022-03-29 21:29:14
select id,LISTAGG(DISTINCT APN_OL,',') AS APN_NEW
from
(SELECT id, pid,
listagg(DISTINCT apn_nbr, ';') within GROUP(ORDER BY apn_nbr)as APN_OL
FROM
(SELECT max(pid) as pid,
f1.id,
apn_nbr,
date
FROM table1 f1
JOIN table2 f2
ON f1.process_id = f2.process_id
WHERE apn_nbr is not null AND id=1227521
GROUP BY id,apn_nbr,date)
GROUP BY id,pid;当我尝试下面的查询时，我得到的记录如下所述：
ID PID APN_NBR
1227521 964306012133700 238885004,130050106,195050142,960109430
1227521 816449643060121 105450046,105450314,136010476,136150077我想在一行中显示APN_NBR的所有记录(第1行中的ie.Records和第2行中的ie.Records)。因此，我尝试了以下逻辑：
select id,LISTAGG(DISTINCT APN_OL,',') AS APN_NEW
from
(SELECT id, pid,
listagg(DISTINCT apn_nbr, ';') within GROUP(ORDER BY apn_nbr)as APN_OL
FROM
(SELECT max(pid) as pid,
f1.id,
apn_nbr,
date
FROM table1 f1
JOIN table2 f2
ON f1.process_id = f2.process_id
WHERE apn_nbr is not null AND id=1227521
GROUP BY id,apn_nbr,date)
GROUP BY id,pid)
GROUP BY id;当我使用上面的查询时，我在一行中得到了APN_NBR的值。
但是，我需要在SELECT语句中添加pid，以便使用另一个表执行联接操作。我需要基于ID和PID列进行连接
回答 1
Stack Overflow用户
回答已采纳
发布于 2022-03-29 22:30:53
因此，您可以将第二个LISTAGG移动到第一个LISTAGG中(除非您希望使用半逗号分隔和逗号分隔的值，然后创建一个PID数组并连接到所有匹配行，如下所示：
select
a.id,
a.APN_OL as apn_new
b.<stuff from pids stuff>
from
(
SELECT
id,
array_agg(distinct pid) as pids,
listagg(DISTINCT apn_nbr, ';') within GROUP(ORDER BY apn_nbr)as APN_OL
FROM (
SELECT
max(pid) as pid,
f1.id,
apn_nbr
FROM table1 f1
JOIN table2 f2
ON f1.process_id = f2.process_id
WHERE apn_nbr is not null AND id = 1227521
GROUP BY id, apn_nbr
)
GROUP BY id
) as A
JOIN table_with_pids_details as B
on ARRAY_CONTAINS(b.pid::variant, a.pids);或
如果PID都有相同的值，但您只需要一个，那么ANY_VALUE()就会有帮助。
select
a.id,
a.APN_OL as apn_new
b.<stuff from pids stuff>
from
(
SELECT
id,
ANY_VALUE(distinct pid) as random_pid,
listagg(DISTINCT apn_nbr, ';') within GROUP(ORDER BY apn_nbr)as APN_OL
FROM (
SELECT
max(pid) as pid,
f1.id,
apn_nbr
FROM table1 f1
JOIN table2 f2
ON f1.process_id = f2.process_id
WHERE apn_nbr is not null AND id = 1227521
GROUP BY id, apn_nbr
)
GROUP BY id
) as A
JOIN table_with_pids_details as B
on b.pid = a.random_pid;页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/71668974
复制相关文章
相似问题
腾讯云开发者
