附件：第1次提的需求
--1.处理前的数据如下
WITH TB1 AS
(SELECT 1 LSH,'001' USERID from DUAL
UNION ALL
SELECT 2,'001' FROM DUAL
UNION ALL
SELECT 3,'001' FROM DUAL
UNION ALL
SELECT 4,'002' FROM DUAL
UNION ALL
SELECT 5,'003' FROM DUAL
UNION ALL
SELECT 6,'008' FROM DUAL
UNION ALL
SELECT 7,'003' FROM DUAL
UNION ALL
SELECT 8,'003' FROM DUAL
UNION ALL
SELECT 9,'002' FROM DUAL
UNION ALL
SELECT 10,'002' FROM DUAL
)
SELECT * FROM TB1;
LSH USERID
1 001
2 001
3 001
4 002
5 003
6 008
7 003
8 003
9 002
10 002
--2.希望得到的结果：
1）同一个USERID，如果LSH连续，如何为其生成相同的NEW_LSH
2）NEW_LSH不能重复使用
3）不用游标
LSH USERID NEW_LSH
1 001 1
2 001 1
3 001 1
4 002 4
5 003 5
6 008 6
7 003 7
8 003 7
9 002 9
10 002 9
可以达到要求的sql语句:
第一种方法:
select lsh, userid, min(lsh) over(partition by userid, flag) new_lsh
from (SELECT lsh,
userid,
case
when userid = lead(userid)
over(order by rownum) or userid = lag(userid)
over(order by rownum) then
1
else
0
end flag
FROM TB1)
order by lsh;
第二种方法:
t1 as
(select lsh,
userid,
lsh - row_number() over(partition by userid order by lsh) rn
from tmp),
t2 as(
select userid, rn, min(lsh) new_lsh from t1 group by userid, rn)
select a.lsh,a.userid, b.new_lsh
from t1 a
left join t2 b
on a.userid = b.userid
and a.rn = b.rn
order by 1;
第三种方法:(11g的递归with)
TMP(LSH,USERID,LEV) AS(
SELECT LSH,USERID,1 LEV FROM TB1 T WHERE ROWNUM<=1
UNION ALL
SELECT T1.LSH,T1.USERID,CASE WHEN T1.USERID=T2.USERID THEN LEV ELSE T1.LSH END FROM TMP T2,TB1 T1 WHERE T1.LSH-T2.LSH=1
)
select * from tmp;
第四种方法:
select x.*, last_value(flag ignore nulls) over(order by lsh) new_lsh
from (select t.*,
decode(userid, lag(userid) over(order by lsh), null, lsh) flag
from TB1 t) x;
---------------------------
之后要求所有改变
--1.处理前的数据如下
WITH TB1 AS
(SELECT 1 LSH,'001' USERID from DUAL
UNION ALL
SELECT 2,'001' FROM DUAL
UNION ALL
SELECT 3,'001' FROM DUAL
UNION ALL
SELECT 4,'002' FROM DUAL
UNION ALL
SELECT 5,'003' FROM DUAL
UNION ALL
SELECT 6,'008' FROM DUAL
UNION ALL
SELECT 7,'003' FROM DUAL
UNION ALL
SELECT 8,'003' FROM DUAL
UNION ALL
SELECT 9,'002' FROM DUAL
UNION ALL
SELECT 10,'002' FROM DUAL
UNION ALL
SELECT 13,'001' FROM DUAL
UNION ALL
SELECT 15,'002' FROM DUAL
UNION ALL
SELECT 17,'001' FROM DUAL
UNION ALL
SELECT 19,'001' FROM DUAL
)
select * from tb1;
LSH USERID
1 001
2 001
3 001
4 002
5 003
6 008
7 003
8 003
9 002
10 002
13 001
15 002
17 001
19 001
--希望得到的结果
LSH USERID NEW_LSH
1 001 1
2 001 1
3 001 1
4 002 4
5 003 5
6 008 6
7 003 7
8 003 7
9 002 9
10 002 9
13 001 13
15 002 15
17 001 17
19 001 17
--希望得到的结果
1）同一个USERID，如果LSH连续（17,19两个序号算连续），如何为其生成相同的NEW_LSH
2）NEW_LSH不能重复使用
3）不用游标
以上的sql只有第四种方依旧有效
第四种方法:
select x.*, last_value(flag ignore nulls) over(order by lsh) new_lsh
from (select t.*,
decode(userid, lag(userid) over(order by lsh), null, lsh) flag
from TB1 t) x;