为丢失的版本创建重复的值

Question

我有一个下表

contact|version|col1|col2|col3
1   |   1   |abc | 57 | X2
4   |   4   |def | 59 | X3
5   |   5   |xyz | 56 | X4
7   |   7   |plo | 58 | X5

所以我有版本1,4,5和7，但没有版本2,3,6。因此，对于所有缺失的版本，我希望通过复制所有列的先前版本值来创建这些版本，如下所示。我不想丢失任何版本号。

contact|version|col1|col2|col3
1   |   1   |abc | 57 | X2
1   |   2   |abc | 57 | X2
1   |   3   |abc | 57 | X2
4   |   4   |def | 59 | X3
5   |   5   |xyz | 56 | X4
5   |   6   |xyz | 56 | X4
7   |   7   |plo | 58 | X5
Please suggest how can this be done.Appreciate in advance.

Answer 1

试试这个-一个连接到输入数据的整数列表：

WITH
-- your input, don't use in final query ..
indata(contact,version,col1,col2,col3) AS (
SELECT 1,1,'abc',57,'X2'
UNION ALL SELECT 4,4,'def',59,'X3'
UNION ALL SELECT 5,5,'xyz',56,'X4'
UNION ALL SELECT 7,7,'plo',58,'X5'
)
-- end of input - replace following comma with "WITH"
,
-- need a series of integers --
i(i) AS (
SELECT 1 
UNION ALL SELECT 2 
UNION ALL SELECT 3 
UNION ALL SELECT 4 
UNION ALL SELECT 5 
UNION ALL SELECT 6 
UNION ALL SELECT 7 
UNION ALL SELECT 8 
UNION ALL SELECT 9 
)
SELECT 
NVL(contact,LAST_VALUE(contact IGNORE NULLS) OVER (ORDER BY i)) AS contact
, i AS version
, NVL(col1,LAST_VALUE(col1 IGNORE NULLS) OVER (ORDER BY i)) AS col1
, NVL(col2,LAST_VALUE(col2 IGNORE NULLS) OVER (ORDER BY i)) AS col2
, NVL(col3,LAST_VALUE(col3 IGNORE NULLS) OVER (ORDER BY i)) AS col3
FROM i
LEFT JOIN indata ON version = i 
WHERE i <=(SELECT MAX(version) FROM indata);
;
-- out  contact | version | col1 | col2 | col3 
-- out ---------+---------+------+------+------
-- out        1 |       1 | abc  |   57 | X2
-- out        1 |       2 | abc  |   57 | X2
-- out        1 |       3 | abc  |   57 | X2
-- out        4 |       4 | def  |   59 | X3
-- out        5 |       5 | xyz  |   56 | X4
-- out        5 |       6 | xyz  |   56 | X4
-- out        7 |       7 | plo  |   58 | X5