sql重复数据处理

最新推荐文章于 2023-04-13 18:01:54 发布

gdjlc

最新推荐文章于 2023-04-13 18:01:54 发布

阅读量746

点赞数

分类专栏： SQL 文章标签： sql table delete

本文链接：https://blog.csdn.net/gdjlc/article/details/3027518

版权

SQL 专栏收录该内容

22 篇文章 0 订阅

订阅专栏

假设表sysparm的数据如下：

/*
Guid     DeptGuid   ActiveTime  Rate
-------- ---------- ---------- -------
b1       d1         2013-10-01  0.50
b1       d1         2013-10-09  0.40
b2       d2         2013-11-05  0.60

(3 行受影响)

*/

现在想取出ActiveTime最近的记录，Guid 和 DeptGuid相同只取一条记录
解决方法（1）：

select s1.* from sysparm s1
inner join (
    select guid,deptguid,
    MAX(activetime) activetime from sysparm
    group by guid,deptguid
) s2
on s1.guid=s2.guid and s1.deptguid=s2.deptguid and s1.activetime=s2.activetime;

/*结果：
Guid         DeptGuid    ActiveTime   Rate
------------ ----------- ---------- -------
b2           d2          2013-11-05   0.60
b1           d1          2013-10-09   0.40

(2 行受影响)
*/

解决方法（2）：

select * from (
    select *,row_number() over(partition by guid,deptguid order by activetime desc) as Sequence
    from sysparm
) t where Sequence=1;

/*结果：
Guid    DeptGuid   ActiveTime   Rate    Sequence
------- ---------- ---------- --------- ----------
b1      d1         2013-10-09   0.40    1
b2      d2         2013-11-05   0.60    1

(2 行受影响)
*/

-------------------------------------------------------------------

另外：

如果需要删除重复的记录（重复记录保留1条），可以按以下方法删除

select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp

另一种方法是一个字段重复,ID不重复

delete 表 where id not in(
SELECT MAX(id) AS id FROM 表 GROUP BY rows) --- 删除重复行

select * from 表 where id in(
SELECT MAX(id) AS id FROM 表 GROUP BY rows) --重复行只查询一条