Oracle listagg去重distinct的方式有哪些

小億
367
2024-03-02 19:48:29
欄目: 云計(jì)算

在Oracle中，可以使用不同的方法來(lái)實(shí)現(xiàn)在使用LISTAGG函數(shù)時(shí)去重重復(fù)的值。以下是一些常用的方法：

  1. 使用子查詢和ROW_NUMBER()函數(shù)：
SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_name) AS result
FROM (
    SELECT column_name, ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name) rn
    FROM table_name
)
WHERE rn = 1;
  1. 使用GROUP BY子句：
SELECT LISTAGG(DISTINCT column_name, ',') WITHIN GROUP (ORDER BY column_name) AS result
FROM table_name;
  1. 使用CONNECT BY子句：
SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_name) AS result
FROM (
    SELECT column_name, ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name) rn
    FROM table_name
)
START WITH rn = 1
CONNECT BY PRIOR column_name = column_name AND PRIOR rn = rn-1;

這些方法可以根據(jù)具體需求選擇合適的方式來(lái)去重重復(fù)的值。

0 踩