在 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
并将其与所有详细信息一起返回,如下所示:它会给你这样的输出:
现在您可以应用到该行集并从它匹配的聚合中
LISTAGG
排除。要排除一年,您可以使用如下表达式:BJA
CONSTRYEAR
CASE
或者您可以使用等效的
NULLIF
速记:CASE
它的工作原理与上面的表达式完全相同。现在你想把这个表达式放在里面
LISTAGG
:这是完整的查询:
输出:
提供现场演示:
Regexp_replace 有很长的路要走:
见分贝小提琴
因此,为了实现所需的输出,初始查询被分成两部分,分别用于选择
"CONSTRYEAR"
和选择"RECONSTRYEAR"
。对于
"CONSTRUCT"
:对于
"RECONSTRYEAR"
: