insert into MyTable
values (1,4533660,1,'COMPANY 1')
insert into MyTable
values (2,4533660,1,'COMPANY 1')
insert into MyTable
values (3,954189547,1,'COMPANY 2')
insert into MyTable
values (4,954189547,1,'COMPANY 2')
insert into MyTable
values (5,3652591,1,'COMPANY 3')
insert into MyTable
values (6,4201580,1,'COMPANY 4')
insert into MyTable
values (7,3337788,1,'COMPANY 5')
然后查询根据预定义的条件获取重复项。
Select
Col1,
Col2,
COUNT(*)
from MyTable
group by Col1,
Col2
HAVING COUNT(*) > 1
使用此查询,您可以轻松找到重复项,比查询本身更重要的是您使用的条件。
如果要消除重复数据(显然建议进行备份或将所有内容封装在事务中),可以执行以下操作:
DELETE A
FROM MyTable A
INNER JOIN
(
SELECT ROW_NUMBER()OVER(PARTITION BY a.col1,
a.Col2
ORDER BY a.Col1,a.Col2)AS POS,
a.rowID,
a.Col3
FROM MyTable A
JOIN
(
Select
Col1,
Col2,
COUNT(*) AS CONTADOR
from MyTable
group by Col1,
Col2
HAVING COUNT(*) > 1
) TB
ON A.col1 = TB.col1
AND A.col2 = TB.col2
) TB_2
ON
a.ROWID = tb_2.ROWID
and tb_2.pos > 1
-- Los puntos 1 y 2 se podrían hacer en un solo paso: crear tabla temporal e insertar datos
INSERT INTO #tablaTemporal
SELECT DISTINCT Col1, Col2, Col3
FROM MyTable;
-- Borrar todos los datos de la tabla original
DELETE FROM MyTable;
-- Volcar los datos no duplicados desde la tabla temporal a la original
INSERT INTO MyTable
SELECT * FROM #tablaTemporal;
DELETE FROM MyTable
WHERE RowID IN (SELECT RowID
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY Col1,Col2,Col3 ORDER BY (SELECT NULL)) AS R_ID,RowID,Col1,Col2,Col3
FROM MyTable) a
WHERE R_ID > 1)
下面的表格也有效,并且在没有区分记录的列的情况下很有用(在您指出的情况下,它具有 RowId)。但我不知道它是否取决于 SQL Server 的版本,它在 SQL Server 2012 中对我有用:
WITH FUENTE AS (
SELECT ROW_NUMBER() OVER(PARTITION BY Col1,Col2,Col3 ORDER BY (SELECT NULL)) AS R_ID,Col1,Col2,Col3
FROM MyTable) DELETE FROM FUENTE WHERE R_ID > 1;
Declare @Fecha Date, @Liquidacion Int, @IdPersonal Int;
DECLARE CN CURSOR FOR
SELECT Fecha, Liquidacion, IdPersonal FROM Personal_tarjas
Where IdPersonal = 9
GROUP BY Fecha, Liquidacion, IdPersonal
HAVING count(*) > 1 And IdPersonal <> 0
OPEN CN;
FETCH NEXT FROM CN Into @Fecha, @Liquidacion, @IdPersonal;
WHILE @@FETCH_STATUS = 0
Begin
WHILE (Select Count(*) From Personal_tarjas Where Fecha = @Fecha And
Liquidacion = @Liquidacion And IdPersonal = @IdPersonal AND IdPersonal = 9) > 1
BEGIN
Update Top(1) Personal_tarjas Set IdPersonal = 0 Where Fecha = @Fecha
And Liquidacion = @Liquidacion And IdPersonal = @IdPersonal AND IdPersonal = 9;
If (Select Count(*) From Personal_tarjas Where Fecha = @Fecha And
Liquidacion = @Liquidacion And IdPersonal = @IdPersonal AND IdPersonal = 9) <= 1
BREAK;
Else
CONTINUE;
END;
FETCH NEXT FROM CN Into @Fecha, @Liquidacion, @IdPersonal;
End
CLOSE CN;
DEALLOCATE CN;
SELECT ROW_NUMBER() OVER (PARTITION BY A.[ID],A.[NRO_SS] ORDER BY A.[ID],A.[NRO_SS] ) AS POS,*
INTO #DM_TdeInterFallasDrive FROM [dbImport].[dbo].[DM_TdeInterFallasDrive] A
DELETE FROM #DM_TdeInterFallasDrive WHERE POS > 1
DROP TABLE [dbImport].[dbo].[DM_TdeInterFallasDrive]
SELECT * INTO [dbImport].[dbo].[DM_TdeInterFallasDrive] FROM #DM_TdeInterFallasDrive
DROP TABLE #DM_TdeInterFallasDrive
ALTER TABLE [dbImport].[dbo].[DM_TdeInterFallasDrive]
DROP COLUMN POS
SELECT * FROM [dbImport].[dbo].[DM_TdeInterFallasDrive]
首先,你需要决定你的口是心非的标准是什么。例如,我想测试第二列 (col1) 和第三列 (col2) 是否一起重复。
先考试日期
然后查询根据预定义的条件获取重复项。
使用此查询,您可以轻松找到重复项,比查询本身更重要的是您使用的条件。
如果要消除重复数据(显然建议进行备份或将所有内容封装在事务中),可以执行以下操作:
一种选择是使用临时表。为了它:
代码(我还没有测试过)会是这样的:
考虑到重复将发生在其他列中,并且有一个列,如果它不同 (RowID) 将仅使用 ROW_NUMBER 函数枚举,由除 RowID 之外的所有列分区并消除这些记录,在这种情况下编号> 1,我不知道这是否是最好的方法,但这是一个选择:
下面的表格也有效,并且在没有区分记录的列的情况下很有用(在您指出的情况下,它具有 RowId)。但我不知道它是否取决于 SQL Server 的版本,它在 SQL Server 2012 中对我有用:
朋友们好:我附上我开发的 Transact,无需额外的表格即可工作。我不会删除重复的行,但是可以将 Update 行更改为 Delete 行。
我希望它为你服务。胡安卡洛斯