After executing SQL table insert statements appear a large number of duplicate data, code is the only, ID is to line up with the serial number, how to write a composition statement delete ID larger what data?

CodePudding user response:

Please provide the table structure, test data, processing logic and hope the result.

CodePudding user response:

According to the code group, id ascending order, delete id not just 1
You can refer to the following code

 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- test data -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
The create table dul (id int, code varchar (255)) 
Insert into dul values (1, 'code1) 
, (2, 'code1), (3,' code1), (4, 'code2), (5,' code2) 
Select * from dul 

-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- remove duplicate code id bigger -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
With cte as (
Select id, code, ROW_NUMBER () over (partition by code order by id) as n the from dul 
) 
The delete from cte where n<> 1 

Raw data:

After deleting data:

CodePudding user response:

- it is to remove the id of the results
Select a. * from table_name a
Where the exists (select 1 from (select code, min (id) as min_id from table_name group by code) b where a.c ode=biggest ode and Anderson d> B.m in_id
)
Go
/*
- test results no problem to perform delete
Select a. * from table_name a
Where the exists (select 1 from (select code, min (id) as min_id from table_name group by code) b where a.c ode=biggest ode and Anderson d> B.m in_id
)
Go
*/

CodePudding user response:

- test results no problem to perform delete
The delete from table_name a
Where the exists (select 1 from (select code, min (id) as min_id from table_name group by code) b where a.c ode=biggest ode and Anderson d> B.m in_id
)
Go
*/

CodePudding user response:

The second floor can

CodePudding user response:

Using a window function, delete duplicate values? - take a look at
Our group every time delete duplicate data, using this method,

CodePudding user response:

4th floor can

CodePudding user response:

Select id, code from table group by ` code ` order by ` id ` asc.
Through the above statement is selected only code, and then through the program will contain the code of the id of the string, such as STR=(1,2,3,5... );
To execute SQL: delete * from table where id not in STR; After delete all the duplicate code,