要实现如下效果：
A BB BB C DD
1 1.1 1.2 2 2.1
请问SQL怎么写？
------解决方案--------------------
with test as (
select 'A' AS STR FROM DUAL
UNION ALL
select 'A' AS STR FROM DUAL
UNION ALL
select 'BB' AS STR FROM DUAL
UNION ALL
select 'BB' AS STR FROM DUAL
UNION ALL
select 'BB' AS STR FROM DUAL
UNION ALL
select 'C' AS STR FROM DUAL
UNION ALL
select 'DD' AS STR FROM DUAL
UNION ALL
select 'E' AS STR FROM DUAL
)
SELECT DECODE(STRL, 1, RN2, 2, LAG(RN2, RN1) OVER(ORDER BY STR) + RN1 / 10),
STR
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY STR ORDER BY STR) AS RN1,
LENGTH(STR) AS STRL,
ROW_NUMBER() OVER(PARTITION BY LENGTH(STR) ORDER BY STR) AS RN2,
STR
FROM TEST)
======================================
1 1 A
2 2 A
3 2.1 BB
4 2.2 BB
5 2.3 BB
6 3 C
7 3.1 DD
8 4 E