A 1 A 2 A 3 XX 3.1 XX 3.2 XX 3.3 A 4 A 5 XX 5.1 A 6 A 7 A 8怎么写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
A 2
A 3
XX 3.1
XX 3.2
XX 3.3
A 4
A 5
XX 5.1
A 6
A 7
A 8怎么写SQL实现?
右边那一列的效果
左边是我模拟的假数据。
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