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;
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;
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;
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;
3条答案
按热度按时间dbf7pr2w1#
使用
RANK
解析函数过滤第一行:UNIQUE
值,则RANK
(或DENSE_RANK
)分析函数将在最新创建日期有两行或更多行时过滤最新创建日期;如果你使用ROW_NUMBER
分析函数,那么当有关系时,你仍然会得到最新创建日期的示例。*其中,对于样本数据:
然后,当绑定变量为1、2或3时,输出为:
| VALUE_LIST|
| --------------|
| 联系我们|
当bind变量为4时,输出为:
| VALUE_LIST|
| --------------|
| C - B - A|
当bind变量为5时,输出为:
| VALUE_LIST|
| --------------|
| A|
fiddle
更新:
根据OP的注解,他们似乎希望在存在具有该值和最新创建日期的任何行时筛选值。在这种情况下,您可以:先用
id
过滤;按值聚合以查找每个值的最新日期和整个结果集的最大日期;然后过滤以忽略最新日期;并与LISTAGG
聚合:fiddle
qvsjd97n2#
创建一个CTE来标记最近的值,然后添加一个
where
条件来不选择它:oipij1gg3#
这似乎是正确的答案
但是,这不会为只有一行的项目给予null。为此,您需要执行以下操作: