时间戳

首页 > 解决方案 > Oracle获取迭代表的最大元素

sql - Oracle获取迭代表的最大元素

问题描述

我正在使用这样的表：

create table example_table (ID Number(8), Year Number(8), Data Varchar(4));
insert into example_table
(ID,Year,Data)
(
select 1,2008,1 from dual union
select 1,2010,2 from dual union
select 2,2014,3 from dual union
select 2,2020,4 from dual union
select 2,2009,5 from dual union
select 3,2003,6 from dual union
select 4,2015,7 from dual union
select 4,2014,8 from dual);
select * from example_table;
ID 年 数据
1 2008年 1
1 2010 2
2 2014 3
2 2020 4
2 2009 5
3 2003年 6
4 2015 7
4 2014 8

在这种情况下，ID 和 Year 列一起形成了特定行的唯一标识符。我只想提取给定 ID 的年份最大化的行，例如

ID 年 数据
1 2010 2
2 2020 4
3 2003年 6
4 2015 7

我知道我可以使用相关的子查询来做到这一点，例如

select distinct 
       ID, Year, Data
from   example_table a
where  Year = 
       (select max(year)
        from   example_table b
        where  a.id = b.id);

或者我可以使用公用表表达式来做到这一点：

with tmp(ID,Year) 
as (
    select distinct 
           ID,
           max(year) 
           over (partition by ID)
    from example_table)
select distinct 
       ID, Year, Data
from   example_table
       inner join tmp 
         on  example_table.ID = tmp.ID 
         and example_table.year = tmp.year;

从技术上讲，我也可以通过创建另一个表/视图来做到这一点，但我的数据库没有权限来做到这一点。无论如何，这是我必须在我的脚本中完成的一项常见任务，我想减少我生成的查询数量。有没有办法只用一个查询来做到这一点？

我尝试使用HAVING如下语句：

select example_table.ID,
       max(example_table.YEAR),
       example_table.DATA 
from example_table
group by ID, DATA
having example_table.YEAR = max(example_table.YEAR);

但这似乎不起作用，因为HAVING语句只对组起作用，我想操纵组内的元素。

有没有办法只用一个查询来做到这一点，如果没有，用两个查询最清楚的方法是什么？

标签： sqloraclegreatest-n-per-group

解决方案

为什么不只使用聚合？

select id, max(year), 
       max(data) keep (dense_rank first order by year desc) as data
from t
group by id;

该keep语法是 Oracle 实现“first_value”聚合函数的方式。

推荐阅读