如何通过单个查询删除表中字段的重复值？

发布于 2024-09-06 11:03:14 字数 196 浏览 3 评论 0原文

我想从表中删除日期字段具有多个值的条目。

假设我有员工表 - Id、Name、Date、Points

我想删除具有相同日期字段的条目，该字段应该是唯一的......只是为了清理我只需要保留一个日期条目并删除其余条目...如果可能的话，也许保留最近的条目...

任何人都可以建议更新查询来执行此操作吗？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

淡看悲欢离合 2024-09-13 11:03:14

使用：

DELETE FROM EMPLOYEE
 WHERE id NOT IN (SELECT MAX(e.id)
                    FROM EMPLOYEE e
                   WHERE e.date = date
                GROUP BY e.date)

由于 WHERE 子句和 GROUP BY 不太可能是必需的。仅返回带有聚合函数的一列，但为了安全起见，将其包含在内。这是假设当OP说日期字段时，SQL Server 2008意味着 DATE 数据类型，而不是 DATETIME。

Use:

DELETE FROM EMPLOYEE
 WHERE id NOT IN (SELECT MAX(e.id)
                    FROM EMPLOYEE e
                   WHERE e.date = date
                GROUP BY e.date)

The GROUP BY isn't likely to be necessary due to the WHERE clause & only returning one column with an aggregate function on it, but it's included to be safe. This is assuming that when the OP says date field, being SQL Server 2008 that means the DATE data type, not DATETIME.

回复收藏 0 原文

神也荒唐 2024-09-13 11:03:14

此查询查看具有相同 ID、名称和点的记录，并删除除最新记录之外的所有记录

with cte as(
    select id,Name, Date, Points,row_number() over(
       partition by id,name,points order by date desc) as ind
    from emp)

    delete from cte where ind>1

this query looks at records with same Id,Name and Points and deletes all but the latest

with cte as(
    select id,Name, Date, Points,row_number() over(
       partition by id,name,points order by date desc) as ind
    from emp)

    delete from cte where ind>1

回复收藏 0 原文

就像说晚安 2024-09-13 11:03:14

如果你的表有主键，你可以通过 dup 条件将表连接到自身并过滤掉更大的 PK，如下所示：

delete e2
from Employee e
join Employee e2 on e.Date=e2.Date
where e.ID < e2.ID

If your table has primary key, you can join the table to itself by the dup condition and filter out greater PKs, something like the following:

delete e2
from Employee e
join Employee e2 on e.Date=e2.Date
where e.ID < e2.ID

回复收藏 0 原文

谁的年少不轻狂 2024-09-13 11:03:14

可以使用，或者您可以使用此代码

DELETE
From Employee 
WHERE ID not in (select max(ID) from Employee group by Name)

如果最后一个条目是最近的日期，则

DELETE
From Employee 
WHERE ID not in 
(select max(ID) from Employee e1 
where Date=(select max(Date) From Employee where Name=e1.Name) 
group by Name)

you can use

DELETE
From Employee 
WHERE ID not in (select max(ID) from Employee group by Name)

if the last entry is the recent date, Or you can use this code

DELETE
From Employee 
WHERE ID not in 
(select max(ID) from Employee e1 
where Date=(select max(Date) From Employee where Name=e1.Name) 
group by Name)

回复收藏 0 原文

~没有更多了~