SQL去掉重复数据只留一条

最新推荐文章于 2023-04-20 19:53:53 发布

最新推荐文章于 2023-04-20 19:53:53 发布 · 174 阅读

数据库同时被 2 个专栏收录

64 篇文章

订阅专栏

数据仓库

6 篇文章

订阅专栏

本文深入探讨了如何通过删除重复记录、整合数据集和利用排名算法来优化数据库性能。通过实例展示了使用SQL查询语句，如SELECT DISTINCT、GROUP BY和窗口函数，实现数据去重、按关键字段排序并筛选出唯一值的过程，从而提升数据管理的效率与准确性。

select distinct khh,khjlh,jlrq into zj_khh_khjlh from crmo_yxzc_khdygx where gxlx='01'

select khh,khjlh,jlrq,rank()over(order by khh,jlrq desc,khjlh) as rankno into zj_khh_khjlh_cf from zj_khh_khjlh where khh in (
select khh from zj_khh_khjlh group by khh having count(1)>1)

delete from zj_khh_khjlh where khh in (
select khh from zj_khh_khjlh group by khh having count(1)>1)

insert into zj_khh_khjlh
select khh,khjlh,jlrq from zj_khh_khjlh_cf a where not exists (select 1 from zj_khh_khjlh_cf b where a.khh = b.khh and a.rankno>b.rankno)