如何:Oracle分析函数返回行

Question

我想我的问题可以用一个Oracle分析功能的SQL，但我不是来确定。假设我有以下文档数据库表:

• Rank:反向顺序，每个文档都有自己的顺序，最近的文档修订具有最低(0)号
• 版本号:每个文件的Alpha数字顺序，最新的文件版本号最高
只读的 只读的

Answer 1

像这样?

SQL> with test (name, rank, revision, state) as
2    (select 'A', 0, '5b', 'ReadOnly'  from dual union all
3     select 'A', 2,  '4', 'Published' from dual union all
4     select 'A', 5,  '1', 'Published' from dual union all
5     select 'B', 0,  '2', 'Draft'     from dual union all
6     select 'B', 1,  '1', 'Published' from dual union all
7     select 'C', 0,  '1', 'Published' from dual
8     )
9  select name, rank, revision, state
10  from (select t.*,
11          rank() over (partition by name order by revision desc, rank) rn
12        from test t
13        where state = 'Published'
14       )
15  where rn = 1;
N       RANK RE STATE
- ---------- -- ---------
A          2 4  Published
B          1 1  Published
C          0 1  Published
SQL>

Answer 2

请求的结果集:每个文档的最新发布版本

一个方法不使用窗口函数:

select t.*
from t
where t.state = 'Published' and
t.rank = (select min(t2.rank) 
from t t2
where t2.name = t.name and t2.state = t.state
);

并且，在Oracle中，您甚至可以使用aggregation:

select document, state, min(rank),
min(revision) keep (dense_rank first order by rank) as revision
from t
where state = 'Published'
group by document, state;

窗口函数是一个非常合理的解决方案，但是，它们不是必需的。