--例如:
id NAME VALUE
1 a pp
2 a pp
3 b iii
4 b pp
5 b pp
6 c pp
7 c pp
8 c iii
--id是主键
--要求得到这样的结果
id NAME VALUE
1 a pp
3 b iii
4 b pp
6 c pp
8 c iii
--方法1
DELETE YourTable
WHERE [id] NOT IN (SELECT MAX([id])
FROM YourTable
GROUP BY
(NAME + VALUE))
--方法2
DELETE a
FROM 表 a
LEFT JOIN (
SELECT id = MIN(id)
FROM 表
GROUP BY
NAME,
VALUE
)b
ON a.id = b.id
WHERE b.id IS NULL
--查询及删除重复记录的SQL语句
--查询及删除重复记录的SQL语句
--1、查找表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断
SELECT *
FROM people
WHERE peopleId IN (SELECT peopleId
FROM people
GROUP BY
peopleId
HAVING COUNT(peopleId) > 1)
--2、删除表中多余的重复记录,重复记录是根据单个字段(peopleId)来判断,只留有rowid最小的记录
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)
--3、查找表中多余的重复记录(多个字段)
SELECT *
FROM vitae a
WHERE (a.peopleId, a.seq) IN (SELECT peopleId,
seq
FROM vitae
GROUP BY
peopleId,
seq
HAVING COUNT(*) > 1)
--4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
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)
--5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
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)
--(二)
--比方说
--在A表中存在一个字段“name”,
--而且不同记录之间的“name”值有可能会相同,
--现在就是需要查询出在该表中的各记录之间,“name”值存在重复的项;
SELECT NAME,
COUNT(*)
FROM A
GROUP BY
NAME
HAVING COUNT(*) > 1
--如果还查性别也相同大则如下:
SELECT NAME,
sex,
COUNT(*)
FROM A
GROUP BY
NAME,
sex
HAVING COUNT(*) > 1(三)
--方法一
DECLARE @max integer,
@id integer
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
SELECT @max = @max -1
SET ROWCOUNT @max
DELETE
FROM 表名
WHERE 主字段 = @id
FETCH cur_rows INTO @id,@max
END
CLOSE cur_rows
SET ROWCOUNT 0
--方法二
--"重复记录"有两个意义上的重复记录,一是完全重复的记录,也即所有字段均重复的记录,二是部分关键字段重复的记录,比如Name字段重复,而其他字段不一定重复或都重复可以忽略。
--1、对于第一种重复,比较容易解决,使用
SELECT DISTINCT *
FROM tableName
--就可以得到无重复记录的结果集。
--如果该表需要删除重复的记录(重复记录保留1条),可以按以下方法删除
SELECT DISTINCT * INTO #Tmp
FROM tableName
DROP TABLE tableName
SELECT * INTO tableName
FROM #Tmp
DROP TABLE #Tmp
--发生这种重复的原因是表设计不周产生的,增加唯一索引列即可解决。
--2、这类重复问题通常要求保留重复记录中的第一条记录,操作方法如下
--假设有重复的字段为Name,Address,要求得到这两个字段唯一的结果集
SELECT IDENTITY(INT, 1, 1) AS autoID,
* INTO #Tmp
FROM tableName
SELECT MIN(autoID) AS autoID INTO #Tmp2
FROM #Tmp
GROUP BY
NAME,
autoID
SELECT *
FROM #Tmp
WHERE autoID IN (SELECT autoID
FROM #tmp2)
--最后一个select即得到了Name,Address不重复的结果集(但多了一个autoID字段,实际写时可以写在select子句中省去此列)(四)
--查询重复
SELECT *
FROM tablename
WHERE id IN (SELECT id
FROM tablename
GROUP BY
id
HAVING COUNT(id) > 1)
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步