SQL server to heavy-CodePudding

A table data inside there is part of the data, I want to repeat the deletion of only one, how to do?

CodePudding user response:

You the red box of the column, the data is not completely the same,

For a data exactly the same two columns to repeat (a, b), how do I remove to heavy methods:

 USE tempdb for 
GO 
IF OBJECT_ID (' t ') IS NOT NULL 
DROP TABLE t 
GO 
CREATE TABLE (t 
A INT, 
INT b, 
C INT 
) 
GO 
INSERT INTO t VALUES (1, 2, 3) 
INSERT INTO t VALUES (1, 2, 3) 
INSERT INTO t VALUES (1,2,6) 
INSERT INTO t VALUES (2 and 4) 
INSERT INTO t VALUES (2,3,5) 
INSERT INTO t VALUES (3,1,2) 

; WITH cte AS (
The SELECT ROW_NUMBER () OVER (PARTITION BY a, b ORDER BY c) AS rids, * 
The FROM t 
) 
SELECT * FROM cte WHERE rids=1 
/* 
Rid a b c 
1 1 2 3 
1 2 3 4 
1 1 2 3 
*/

; WITH cte AS (
The SELECT ROW_NUMBER () OVER (PARTITION BY a, b ORDER BY c) AS rids, * 
The FROM t 
) 
The DELETE FROM cte WHERE rid> 1 


SELECT * FROM t 
/* 
A b c 
1 2 3 
2, 3, 4, 
1 2 3 
*/