需求:根据 004V (sid)分组取单个004V(sid) 的005n >0的最大日期
//内部根据004V 分组,根据每个004v生成不同的序列号,然后取第一个
select * from (
select to_char(f0190_001d, 'yyyy-mm-dd') mdate,
F0190_002V,
F0190_003V,
f0190_004v sid,
F0190_005N mlevel,
row_number() over(partition by F0190_004V order by F0190_001D desc) rn from tb_policy_0190
where f0190_005n > 0
) where rn=1
未加rn=1的条件前的结果:
mdate F0190_002V rn
1 2011-04-22 012002 001001 000001 2 1
2 2011-04-20 012002 001001 000001 2 2
3 2011-04-14 012002 001001 000001 1 3
4 2011-04-13 012002 001001 000001 1 4
5 2010-09-03 012002 001001 000001 1 5
6 2010-09-02 012002 001001 000001 1 6
7 2010-05-12 012002 001001 000001 1 7
8 2009-11-13 012002 001001 000001 1 8
9 2009-11-12 012002 001001 000001 1 9
10 2009-06-23 012002 001001 000001 2 10
11 2009-06-22 012002 001001 000001 2 11
12 2009-06-19 012002 001001 000001 2 12
13 2009-06-18 012002 001001 000001 2 13
14 2009-06-17 012002 001001 000001 2 14
15 2009-03-10 012002 001001 000001 1 15
16 2009-03-05 012002 001001 000001 1 16
17 2009-03-04 012002 001001 000001 1 17
71 2011-01-04 012002 001001 000002 3 1
72 2010-12-28 012002 001001 000002 3 2
73 2010-10-20 012002 001001 000002 3 3
74 2010-10-14 012002 001001 000002 3 4
加rn=1后的结果:
mdate F0190_002V rn
1 2011-04-22 012002 001001 000001 2 1
2 2011-04-20 012002 001001 000002 1 1