Use the LAG and LEAD function to find the deleted serial number in the order number

xiaoxiao2021-03-06  90

Tzxd @ webora9> spool aaa.log

TZXD @Webora9> Drop Table Test;

Drop Table Test

*

Error At Line 1:

ORA-00942: Table or view does not exist

TZXD @Webora9> Create Table Test (ID, Object_name) AS

2 Select Rownum, Object_name from obj where rownum <= 20;

Table created.

TZXD @Webora9> Delete from test where id in (5, 9, 15);

Here, we first delete 3 discontinuous data, see how the results

3 rows deleded.

Tzxd @Webora9> Col ​​Object_name Format A32

TZXD @Webora9> SELECT ID, Object_name from test;

ID Object_name

---------- --------------------------------

1 ACC_FEE_CODE

2 ACC_USER_BILL_DTL_576_200401

3 ACC_USER_BILL_DTL_576_200402

4 ACC_USER_BILL_DTL_576_200403

6 ACC_USER_BILL_DTL_576_200405

7 ACC_USER_BILL_DTL_576_200406

8 all_cell_info

10 all_cell_info_temp

11 all_cell_info_temp_1

12 all_cell_info_temp_2

13 all_Cell_INFO_TEMP_3

14 analize_xd

16 centize_xd_post

17 Analize_XD_PRE

18 Area_200406

19 Area_200406_Count

20 Area_200406_Temp1

17 rows selected.

TZXD @Webora9> SELECT ID, ID - LAG_VAL DIFF_1, Lead_VAL - ID DIFF2, LAG_VAL, Lead_VAL,

Object_name 2

3 from

4 SELECT ID, LAG (ID, 1, 0) OVER (Order By ID) LAG_VAL,

5 Lead (ID, 1, 0) OVER (Order By ID) Lead_Val, Object_name

6 from test

7)

8 WHERE LEAD_VAL - ID <> ID - LAG_VAL

9 /

ID DIFF_1 DIFF2 LAG_VAL LEAD_VAL OBJECT_NAME

---------- -------------------------------------- --------------------------------

4 1 2 3 6 ACC_USER_BILL_DTL_576_200403

/ *

5 Lack of DIFF_2 - DIFF_1 = 2 - 1 = 1 Data 5 * /

6 2 1 4 7 ACC_USER_BILL_DTL_576_200405

8 1 2 7 10 All_Cell_Info

/ *

9 behind 8 lack of DIFF_2 - DIFF_1 = 2 - 1 = 1 Data 9 * / 10 2 1 8 11 All_cell_info_temp

14 1 2 13 16 AnALIZE_XD

/ *

15 lack of DIFF_2 - DIFF_1 = 2 - 1 = 1 Data 15 * /

16 2 1 14 17 Analize_XD_POST

20 1 -20 19 0 Area_200406_Temp1

7 rows selected.

Tzxd @Webora9> spool off

转载请注明原文地址:https://www.9cbs.com/read-123785.html

New Post(0)