SQL Server :删除乘法主键上的最大值

Question

我想从我的表中删除仅在主键上有最大不同列数的重复项

Column1PK Column2    Column3    Column4
----------------------------------------
Dog       468        JOURNALING     1
Dog       466        JOURNALING     2
Cat       268        TRAVELING      1
Cat       366        TRAVELING      2
Cat       166        TRAVELING      3
...        ..         ..     


Column1PK  Column2     Column3
-----------------------------------
Dog        468         JOURNALING
Cat        366         TRAVELING
..         ..          ..

我的代码：

WITH CTE AS
(
   SELECT 
       [Column1PK], [Column2], [Column3],
       RN = ROW_NUMBER() OVER (PARTITION BY [Column1PK] ORDER BY [Column1PK])
   FROM 
       [dbo].[table] 
)
DELETE FROM CTE 
WHERE RN > 1 and MAX[Column2] 

我的另一个想法是：

Column1PK  Column3    
----------------------
Dog468     JOURNALING    
Dog466     JOURNALING     
Cat268     TRAVELING      
Cat366     TRAVELING     
Cat166     TRAVELING


Column1PK  Column3       
-------------------------
Dog466     JOURNALING          
Cat366     TRAVELING   

代码：

delete from table 
where Column1PK RIGHT (LEN(MAXColumn1PK))

感谢您的意见如何删除多个主键的最大值

Answer 1

删除次要项

DELETE A
(
   SELECT 
       [Column1PK], [Column2], [Column3],
       RN = ROW_NUMBER() OVER (PARTITION BY [Column1PK] ORDER BY [Column1PK] DESC)
   FROM 
       [dbo].[table] 
) A
WHERE RN > 1

删除最大的

DELETE A
(
   SELECT 
       [Column1PK], [Column2], [Column3],
       RN = ROW_NUMBER() OVER (PARTITION BY [Column1PK] ORDER BY [Column1PK] ASC)
   FROM 
       [dbo].[table] 
) A
WHERE RN > 1

Answer 2

不知道我是否理解正确，但如果您只想删除具有最高值的重复项，则可以使用exists。它看起来像这样：

DELETE FROM Table_A AS A
WHERE EXISTS (SELECT * FROM Table_A AS A2
               WHERE A2.Column1PK = A.Column1PK
                 AND A2?? < A.??)