首先，我有这张表。
CREATE TABLE tabla_1
(table_num NUMBER, amount NUMBER, first_name VARCHAR2(100), l_name VARCHAR2(100));
INSERT INTO tabla_1 (table_num, amount, first_name,l_name) VALUES (1,1,'Luis', 'Rosas');
INSERT INTO tabla_1 (table_num, amount, first_name,l_name) VALUES (1,1,'Carlos','Borolas');
INSERT INTO tabla_1 (table_num, amount, first_name,l_name) VALUES (1,2,'Elena','Zeta');
INSERT INTO tabla_1 (table_num, amount, first_name,l_name) VALUES (1,null,null,null);
INSERT INTO tabla_1 (table_num, amount, first_name,l_name) VALUES (2,null,null,null);
commit;
query result:
TABLE_NUM AMOUNT FIRST_NAME L_NAME
---------- ---------- ---------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------
1 1 Luis Rosas
1 1 Carlos Borolas
1 2 Elena Zeta
1 -1 'N/A' 'N/A'
2 -1 'N/A' 'N/A'
Expected result:
TABLE_NUM AMOUNT FIRST_NAME L_NAME
---------- ---------- ----------------------------------------------------------------------------- ---------------------------------------------------------------
1 1 Luis Rosas
1 1 Carlos Borolas
1 2 Elena Zeta
1 2 Elena Zeta
2 2 Elena Zeta
对不起，西班牙语表，英语不是我的母语。
问题是：
注意：在最后一行中，我有"N/A"，这是因为此值已经插入空值。(我不能在表上插入空值，至少我必须在空数上插入 -1 的东西，在空 varchar2 上插入"n/a"(，这已经用代码完成了。
2.- 如何将此">
null""值替换为用户已知的最后一个不是"""null"值？3.- 哪个last_value或 LAG 更好，我如何使用它？是否可以在整行中使用滞后或最后一个值？
NVL+LAST_VALUE可能是一个选项：
SQL> select table_num,
2 nvl(amount, last_value(amount ignore nulls) over (order by table_num)) amount,
3 nvl(first_name, last_value(first_name ignore nulls) over (order by table_num)) first_name
4 from tabla_1;
TABLE_NUM AMOUNT FIRST_NAME
---------- ------ --------------------
1 1 Carlos
1 1 Luis
1 2 Elena
1 2 Elena
2 2 Elena
SQL>
如果没有NULLs(如示例数据所示(，而是-1和N/A，则使用CASE：
select table_num,
case when amount = -1 then last_value(amount ignore nulls) over (order by table_num)
else amount
end amount,
--
case when first_name = 'N/A' then last_value(first_name ignore nulls) over (order by table_num)
else first_name
end first_name
from tabla_1