SELECT * FROM people
WHERE peopleId IN (
SELECT peopleId FROM people
GROUP BY peopleId HAVING COUNT(peopleId) > 1
);
DELETE FROM people
WHERE peopleId IN (
SELECT peopleId FROM people
GROUP BY peopleId HAVING COUNT(peopleId) > 1
)
AND rowid NOT IN (
SELECT MIN(rowid) FROM people
GROUP BY peopleId HAVING COUNT(peopleId) > 1
);
SELECT * FROM vitae a
WHERE (a.peopleId, a.seq) IN (
SELECT peopleId, seq FROM vitae
GROUP BY peopleId, seq HAVING COUNT(*) > 1
);
DELETE FROM vitae a
WHERE (a.peopleId, a.seq) IN (
SELECT peopleId, seq FROM vitae
GROUP BY peopleId, seq HAVING COUNT(*) > 1
)
AND rowid NOT IN (
SELECT MIN(rowid) FROM vitae
GROUP BY peopleId, seq HAVING COUNT(*) > 1
);
SELECT * FROM vitae a
WHERE (a.peopleId, a.seq) IN (
SELECT peopleId, seq FROM vitae
GROUP BY peopleId, seq HAVING COUNT(*) > 1
)
AND rowid NOT IN (
SELECT MIN(rowid) FROM vitae
GROUP BY peopleId, seq HAVING COUNT(*) > 1
);
-- 單欄位
SELECT Name, COUNT(*) FROM A GROUP BY Name HAVING COUNT(*) > 1;
-- 多欄位
SELECT Name, 性別, COUNT(*) FROM A GROUP BY Name, 性別 HAVING COUNT(*) > 1;
DECLARE @max INT, @id INT;
DECLARE cur_rows CURSOR LOCAL FOR
SELECT 主鍵欄位, COUNT(*) FROM 表名
GROUP BY 主鍵欄位 HAVING COUNT(*) > 1;
OPEN cur_rows;
FETCH cur_rows INTO @id, @max;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @max = @max - 1;
SET ROWCOUNT @max;
DELETE FROM 表名 WHERE 主鍵欄位 = @id;
FETCH cur_rows INTO @id, @max;
END
CLOSE cur_rows;
SET ROWCOUNT 0;
SELECT DISTINCT * INTO #Tmp FROM 表名;
DROP TABLE 表名;
SELECT * INTO 表名 FROM #Tmp;
DROP TABLE #Tmp;
建議:新增唯一索引從根本防止重複。
SELECT IDENTITY(INT,1,1) AS autoID, * INTO #Tmp FROM 表名;
SELECT MIN(autoID) AS autoID INTO #Tmp2 FROM #Tmp GROUP BY Name, Address;
SELECT * FROM #Tmp WHERE autoID IN (SELECT autoID FROM #Tmp2);