我想从我的表中删除主键上的重复性,只有那里是不同列的最大数量
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))
感谢您的意见如何删除多个PK的最大值
删除次要
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
不确定我是否正确理解您,但是如果您只想删除具有最高值的重复项,则可以使用存在。那看起来像这样:
DELETE FROM Table_A AS A
WHERE EXISTS (SELECT * FROM Table_A AS A2
WHERE A2.Column1PK = A.Column1PK
AND A2?? < A.??)