A b c d four fields


Values are 1, 2, 3, 4,
1 2 3 4
8 2 5 1
5 8 9 1
5 8 9 1



After the first two lines and two rows of data are exactly the same, want to achieve: delete data in exactly the same, only keep like data is not the same as the above 1, 2, 5 8, bosses, please grant instruction

CodePudding user response:

Select distinct a, b, c, d into new_table from the table

CodePudding user response:

 
Int the create table # t (a, b, int, int, c d int) 

Insert into # t (a, b, c, d) 
Select 1, 2, 3, 4 union all 
Select 1, 2, 3, 4 union all 
The select 8,2,5,1 union all 
Select 5,8,9,1 union all 
Select 5,8,9,1; 


With t as 
(select *, rn=row_number () over (order by getdate ()) 
The from # t) 
Delete the x 
The from t x 
Where the exists (select 1 
The from t y 
Where y.a=x.a and y.b=x.b and y.c=x.c and y.d=x.d 
And y.r n<> X.r n) 


Select * from # t 

/* 
A b c d 
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- 
8 2 5 1 

Line (1) affected 
*/