论坛徽章:: 0

电梯直达

1楼 [收藏(0)] [报告]

发表于 2016-06-12 16:29 |只看该作者 |倒序浏览

5可用积分

本帖最后由 jacson007 于 2016-06-12 16:53 编辑

有元数据如下：
SSS SN STS
S1 1 R
S1 2 R
S1 3 R
S1 4 S
S1 5 S
S1 6 S
S1 7 S
S1 8 S
S1 9 S
S1 10 R
S1 11 R
S1 12 R
S1 13 R
S1 14 R
S1 15 R
S1 16 R
S1 17 S
S1 18 S
S1 19 S
S1 20 S
S2 1 R
S2 2 R
S2 3 R
S2 4 R
S2 5 R
S2 6 S
S2 7 S
S2 8 S
S2 9 S
S2 10 R
S2 11 R
S2 12 R
S2 13 R
S2 14 R
S2 15 R
S2 16 R
S2 17 S
S2 18 S
S2 19 S

输出如下：
SSS SN STS RN
S1 1 R 1
S1 2 R 1
S1 3 R 1
S1 4 S 2
S1 5 S 2
S1 6 S 2
S1 7 S 2
S1 8 S 2
S1 9 S 2
S1 10 R 3
S1 11 R 3
S1 12 R 3
S1 13 R 3
S1 14 R 3
S1 15 R 3
S1 16 R 3
S1 17 S 4
S1 18 S 4
S1 19 S 4
S1 20 S 4
S2 1 R 1
S2 2 R 1
S2 3 R 1
S2 4 R 1
S2 5 R 1
S2 6 S 2
S2 7 S 2
S2 8 S 2
S2 9 S 2
S2 10 R 3
S2 11 R 3
S2 12 R 3
S2 13 R 3
S2 14 R 3
S2 15 R 3
S2 16 R 3
S2 17 S 4
S2 18 S 4
S2 19 S 4

请高手指点！

文库|博客

jacson007

稍有积蓄

论坛徽章:: 0

2楼 [报告]

发表于 2016-06-12 21:10 来自手机 |只看该作者

突然想到一种方法，明天试试

实战分享：从技术角度谈机器学习入门| 【大话IT】RadonDB低门槛向MySQL集群下战书 | ChinaUnix打赏功能已上线！ | 新一代分布式关系型数据库RadonDB知多少？

li0924

家境小康

论坛徽章:: 7

3楼 [报告]

发表于 2016-08-08 10:32 |只看该作者

回复 2# jacson007
没解决试下我这个

with t(sss,sn,sts) as (
select 's1',1,'r' from dual
union all
select 's1',2,'r' from dual
union all
select 's1',3,'s' from dual
union all
select 's1',4,'s' from dual
union all
select 's1',5,'r' from dual
union all
select 's1',6,'r' from dual
union all
select 's1',7,'s' from dual
union all
select 's1',8,'s' from dual
union all
select 's2',1,'r' from dual
union all
select 's2',2,'r' from dual
union all
select 's2',3,'s' from dual
union all
select 's2',4,'s' from dual
union all
select 's2',5,'r' from dual
union all
select 's2',6,'r' from dual
union all
select 's2',7,'s' from dual
union all
select 's2',8,'s' from dual
)
select sss,sn,sts,dense_rank() over (partition by sss order by rn1,sts) from
(select sss,sn,sts, sn - dense_rank() over (partition by sss,sts order by sn) - decode(sts,'s',1,0) rn1 from t)