sql 数据去重并且保留一条（在一定的时间范围随机获取时间）

SQL数据更新与去重

最新推荐文章于 2025-07-16 14:47:07 发布

原创最新推荐文章于 2025-07-16 14:47:07 发布 · 3.3k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql

数据库学习心得专栏收录该内容

6 篇文章

订阅专栏

本文介绍了一种使用SQL更新特定字段并确保数据唯一性的方法，同时提供了删除重复记录的解决方案，以保持数据库表的整洁。

-- 将lmt作为唯一标识确保唯一

update SWS_ST_SPB_P set lmt = t.lmt
from 
SWS_ST_SPB_P s,
(
SELECT stcd, mpcd, tm, v1, v2, v3, r1, r2, r3,  
DATEADD(second, ABS(CHECKSUM(NEWID())) % DATEDIFF(second,'00:00','23:59'), CONVERT(char(20),tm,20) ) as lmt
from SWS_ST_SPB_P s
) t
where s.stcd = t.stcd and s.mpcd = t.mpcd and s.tm = t.tm 

-- 重复数据只保留一条

DELETE from SWS_ST_SPB_P where 
stcd in (SELECT stcd from SWS_ST_SPB_P GROUP BY stcd, mpcd, tm HAVING count(*)>1)
and tm  in (SELECT tm from SWS_ST_SPB_P GROUP BY stcd, mpcd, tm HAVING count(*)>1)
and mpcd  in (SELECT mpcd from SWS_ST_SPB_P GROUP BY stcd, mpcd, tm HAVING count(*)>1)
and lmt not in (SELECT max(lmt) from SWS_ST_SPB_P GROUP BY stcd, mpcd, tm HAVING count(*)>1)