僅將所需值與 LISTAGG 連接

在 Oracle DB 中有一個包含數據的表"YEARS"，請參見下面的表/圖像

+-----+-----------+------------------+
| FID |    I0     |       BJA        |
+-----+-----------+------------------+
|   1 |     0     |       1949       |
|   2 |     0     |       1996       |
|   3 |     0     |       1970       |
|   4 |     1     |       1871       |  
|   4 |     0     |       1975       |
|   5 |     0     |       1967       |
|   6 |     0     |       1968       |
|   7 |     0     |       1926       |
|   7 |     1     |       2009       |
|   7 |     2     |       2012       |
|   7 |     3     |       2018       |
|   8 |     0     |       1956       |
|   9 |     0     |       1990       |
|  10 |     0     |       1953       |
|  10 |     1     |       1904       |
| ... |    ...    |       ...        |
+-----+-----------+------------------+

我試圖創建一個小提琴（在這裡），但它沒有用……

CREATE TABLE YEARS (
   "FID" NUMBER(10,0) NOT NULL, 
   "I0" NUMBER(10,0) NOT NULL, 
   "BJA" NUMBER(10,0)
);

INSERT ALL
   INTO YEARS ("FID","I0","BJA") VALUES (1,0,1949)
   INTO YEARS ("FID","I0","BJA") VALUES (2,0,1996)
   INTO YEARS ("FID","I0","BJA") VALUES (3,0,1970)
   INTO YEARS ("FID","I0","BJA") VALUES (4,1,1871)
   INTO YEARS ("FID","I0","BJA") VALUES (4,0,1975)
   INTO YEARS ("FID","I0","BJA") VALUES (5,0,1967)
   INTO YEARS ("FID","I0","BJA") VALUES (6,0,1968)
   INTO YEARS ("FID","I0","BJA") VALUES (7,0,1926)
   INTO YEARS ("FID","I0","BJA") VALUES (7,1,2009)
   INTO YEARS ("FID","I0","BJA") VALUES (7,2,2012)
   INTO YEARS ("FID","I0","BJA") VALUES (7,3,2018)
   INTO YEARS ("FID","I0","BJA") VALUES (8,0,1956)
   INTO YEARS ("FID","I0","BJA") VALUES (9,0,1990)
   INTO YEARS ("FID","I0","BJA") VALUES (10,0,1953)
   INTO YEARS ("FID","I0","BJA") VALUES (10,1,1904)
   
SELECT 1 FROM dual;

我正在嘗試執行以下查詢：

SELECT YEARS.FID,
      MIN(YEARS.BJA) AS "CONSTRYEAR",
      LISTAGG(YEARS.BJA, ', ') WITHIN GROUP (ORDER BY YEARS.BJA ASC) AS "RECONSTRYEAR"
FROM DB.YEARS YEARS
GROUP BY YEARS.FID;

以及上述查詢的輸出：

但是，這不是我想要的結果……是的，我正在閱讀文件source 1、source 2和source 3；並且我也看到了相關的執行緒如何一起使用 LISTAGG 和 WHERE以及如何使用帶有唯一過濾器的 Oracle 的 LISTAGG 函式？.

我怎樣才能得到這樣的結果：

+-----+-----------+------------------+
| FID | CONSTRUCT |   RECONSTRYEAR   |
+-----+-----------+------------------+
|   1 |      1949 |                  |
|   2 |      1996 |                  |
|   3 |      1970 |                  |
|   4 |      1871 |             1975 |
|   5 |      1967 |                  |
|   6 |      1968 |                  |
|   7 |      1926 | 2009, 2012, 2018 |
|   8 |      1956 |                  |
|   9 |      1990 |                  |
|  10 |      1904 |             1953 |
+-----+-----------+------------------+

您看到"CONSTRUCT"列中的值被排除在"RECONSTRYEAR". 我不明白我需要在哪裡放置一個WHERE YEARS.IO != 0子句LISTAGG，所以不會包括最低年份。

您可以使用一個視窗MIN()來查找每個施工年份FID並將其與所有詳細資訊一起返回，如下所示：

SELECT
 YEARS.*
, MIN(BJA) OVER (PARTITION BY FID) AS CONSTRYEAR
FROM
 YEARS

它會給你這樣的輸出：

現在您可以應用到該行集並從它匹配的聚合中LISTAGG排除。要排除一年，您可以使用如下表達式：BJA``CONSTRYEAR``CASE

CASE BJA WHEN CONSTRYEAR THEN NULL ELSE BJA END

或者您可以使用等效的NULLIF速記：

NULLIF(BJA, CONSTRYEAR)

CASE它的工作原理與上面的表達式完全相同。

現在你想把這個表達式放在裡面LISTAGG：

LISTAGG(NULLIF(BJA, CONSTRYEAR), ', ') WITHIN GROUP (ORDER BY BJA ASC) AS RECONSTRYEAR

這是完整的查詢：

SELECT
 FID
, CONSTRYEAR
, LISTAGG(NULLIF(BJA, CONSTRYEAR), ', ') WITHIN GROUP (ORDER BY BJA ASC) AS RECONSTRYEAR
FROM
 (
   SELECT
     YEARS.*
   , MIN(BJA) OVER (PARTITION BY FID) AS CONSTRYEAR
   FROM
     YEARS
 )
GROUP BY
 FID
, CONSTRYEAR
;

輸出：

提供現場展示：

• SQL小提琴
• db<>小提琴

Regexp_replace 有很長的路要走：

Regexp_replace( LISTAGG(YEARS.BJA, ', ') WITHIN GROUP (ORDER BY YEARS.BJA ASC),
                  '^[0-9]*(, )?','')

見分貝小提琴

引用自：https://dba.stackexchange.com/questions/286721