下面是表格的示例
id from to value
1 01.01.20 19.01.20 100
1 20.01.20 31.12.99 100
2 01.01.20 19.01.20 1001
2 25.01.20 31.12.99 1001
3 01.01.20 19.01.20 1002
3 29.01.20 31.12.99 1002
目的是检索最新行
预期输出应如下所示
id from to value
1 20.01.20 31.12.99 100
2 25.01.20 31.12.99 1001
3 29.01.20 31.12.99 1002
我尝试做这样的事情:
select *
from test
where id in a
AND from = (select max(from)
from test
where id in a);
但是,我只检索一行
3 29.01.20 31.12.99 1002
感谢任何人的帮助!
您需要使用MAX
和GROUP BY
进行简单聚合:
with t as
(
select 1 id, '01.01.20' "from", '19.01.20' "to", 100 "value" from dual union all
select 1, '20.01.20', '31.12.99', 100 from dual union all
select 2, '01.01.20', '19.01.20', 1001 from dual union all
select 2, '25.01.20', '31.12.99', 1001 from dual union all
select 3, '01.01.20', '19.01.20', 1002 from dual union all
select 3, '29.01.20', '31.12.99', 1002 from dual
)
select id, max("from"), max("to"), "value" from t
group by id, "value"
order by id;
ID from to value
---------- -------- -------- ----------
1 20.01.20 31.12.99 100
2 25.01.20 31.12.99 1001
3 29.01.20 31.12.99 1002
附带说明一下,'01.01.20'
是一个字符串,而不是日期。因此,请确保对日期值使用适当的DATE
数据类型。
我认为您需要每个 ID 的最新行 -
SELECT
FROM (SELECT *, ROW_NUMBER() OVER(PARTITION BY id ORDER BY id, from DESC) RN
FROM test)
WHERE RN = 1;
你似乎想要:
select t.*
from test t
where t.to_date = '31.12.99'