使用RANK解析函数过滤第一行：

SELECT LISTAGG(DISTINCT value, ' - ')
         WITHIN GROUP (ORDER BY created_date DESC) AS value_list
FROM   (
  SELECT value,
         created_date,
         RANK() OVER (ORDER BY created_date DESC) AS rnk
  FROM   MY_TABLE
  WHERE  id = ?
)
WHERE  rnk > 1;

• 注意：如果您试图获取UNIQUE值，则RANK（或DENSE_RANK）分析函数将在最新创建日期有两行或更多行时过滤最新创建日期;如果你使用ROW_NUMBER分析函数，那么当有关系时，你仍然会得到最新创建日期的示例。*

其中，对于样本数据：

CREATE TABLE my_table (id, value, created_date) AS
SELECT 1, 'A', DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 2, 'A', DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 2, 'A', DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 3, 'A', DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 3, 'B', DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 4, 'A', DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 4, 'A', DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 4, 'B', DATE '2023-01-02' FROM DUAL UNION ALL
SELECT 4, 'C', DATE '2023-01-03' FROM DUAL UNION ALL
SELECT 4, 'B', DATE '2023-01-04' FROM DUAL UNION ALL
SELECT 5, 'A', DATE '2023-01-01' FROM DUAL UNION ALL
SELECT 5, 'B', DATE '2023-01-02' FROM DUAL;

然后，当绑定变量为1、2或3时，输出为：
| VALUE_LIST|
| --------------|
| 联系我们|
当bind变量为4时，输出为：
| VALUE_LIST|
| --------------|
| C - B - A|
当bind变量为5时，输出为：
| VALUE_LIST|
| --------------|
| A|
fiddle

更新：

根据OP的注解，他们似乎希望在存在具有该值和最新创建日期的任何行时筛选值。在这种情况下，您可以：先用id过滤;按值聚合以查找每个值的最新日期和整个结果集的最大日期;然后过滤以忽略最新日期;并与LISTAGG聚合：

SELECT LISTAGG(value, ' - ')
         WITHIN GROUP (ORDER BY created_date DESC) AS value_list
FROM   (
  SELECT value,
         MAX(created_date) AS created_date,
         MAX(MAX(created_date)) OVER () AS max_created_date
  FROM   MY_TABLE
  WHERE  id = ?
  GROUP BY value
)
WHERE  created_date < max_created_date;

fiddle

创建一个CTE来标记最近的值，然后添加一个where条件来不选择它：

with my_table_flagged as
(
    select --other values from table
        , row_number() over (partition by t.ID order by t.CREATED_DATE desc) rn
    from my_table t
)
SELECT LISTAGG(UNIQUE(t.VALUE), ' - ')
  WITHIN GROUP (ORDER BY t.CREATED_DATE DESC)
  FROM my_table_flagged t
  WHERE t.id=?
  AND rn <> 1
  GROUP BY t.id;

这似乎是正确的答案

WITH numbered AS 
(
  SELECT x.*, 
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY t.CREATED_DATE DESC) as RN
  FROM MY_TABLE x
)  
SELECT LISTAGG(UNIQUE(t.VALUE), ' - ')
  WITHIN GROUP (ORDER BY t.CREATED_DATE DESC)
FROM numbered t 
WHERE t.id=? AND RN > 1
GROUP BY t.id;

但是，这不会为只有一行的项目给予null。为此，您需要执行以下操作：

WITH numbered AS 
(
  SELECT x.*, 
         ROW_NUMBER() OVER (PARTITION BY id ORDER BY t.CREATED_DATE DESC) as RN
  FROM MY_TABLE x
)  
SELECT LISTAGG(UNIQUE(x.VALUE), ' - ')
  WITHIN GROUP (ORDER BY COALESCE(t.CREATED_DATE,NOW()) DESC)
FROM (
  SELECT DISTINCT id
  FROM MY_TABLE
) X 
LEFT JOIN numbered t ON t.id = X.id AND t.RN > 1
WHERE x.id = ?
GROUP BY x.id;