Oracle Learning Summary (ii)

The main contents of this article include: Implement the self-amplification ID in Oracle, delete the duplicate record in the data table
I. Self-Amplification ID

1. Create sequence first
Create sequence Seqmax increment by 1
2. Get an ID
Select Seqmax.nextval ID from dual
3. To delete a sequence
Drop sequence Seqmax;

Two. Delete duplicate records from a datasheet

1. Create a table first
CREATE TABLE "Apptest" (
' ID ' INTEGER primary key not NULL,
"MOBILE" Nvarchar2 not NULL
2. If there is a large number of mobile phone number repeat, to delete duplicate records, you can have the following two ways:
(1) Simple use of rowid delete
Delete from Apptest a where rowid isn't in (select Max (ROWID) from Apptest b where a.mobile=b.mobile);
It is said that this method is inefficient when the data volume is very high.
(2) Using analytic function
Delete Apptest where rowID in (
Select Rid from
(select rowID rid,row_number () over (partition by mobile ORDER BY id DESC) rn from Apptest)
where rn > 1);(3) Make temp table
</