Delete two duplicate data records in an SQL statement and retain one record.

-- Any test table
Copy codeThe Code is as follows:
Create table test_delete (
Name varchar (10 ),
Value INT
);
Go
-- Zhang San 100 and Wang wu80 are duplicate.
Insert into test_delete
SELECT 'zhang san', 100
Union all select 'zhang san', 100
Union all select 'Li si', 80
Union all select 'wang 5', 80
Union all select 'wang 5', 80
Union all select 'zhao liu', 90
Union all select 'zhao liu', 70
Go
-- Test Data Retrieval
SELECT
ROW_NUMBER () OVER (partition by name, value order by (SELECT 1) AS no,
Name,
Value
FROM
Test_delete


No name value
-----------------------------------------
1 Li Si 80
1 Wang Wu 80
2. Wang Wu 80
1 Zhang San 100
2 Zhang San 100
1 Zhao liu70
1 Zhao liu90

-- Create a view
Copy codeThe Code is as follows:
Create view tmp_view
SELECT
ROW_NUMBER () OVER (partition by name, value order by (SELECT 1) AS no,
Name,
Value
FROM
Test_delete

-- Delete data
1> delete from tmp_view WHERE no! = 1
2> go

(2 rows affected)

-- Check Result
1> www.jb51.net
2> select * from test_delete;
3> go
Name value
---------------------
Zhang San 100
Li Si 80
Wang Wu 80
Zhao liu90
Zhao liu70

(5 rows affected)


Author tearsmo