sql 查询、删除表中重复记录

2011-05-07 10:00:39 216 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

【spark源码修改】hive3.1.3 spark3.5.4编译，需要修改源码，最终编译成功【spark源码修改】hive3.1.3 spark3.5.4编译，需要修改源码，最终编译成功,附带问题分析详细过程阅读详情

弱弱问下;表 music 里面有name,player 等字段，有好些记录是重复的
如何查找出这些多余的重复记录呢？
如何删除这些，重复记录中只保留一条？？



select * from music m 

where (m.name , m.player) in 

(select name ,player from music group by name, player having count(*)>1)





delete from music s  

where (s.name,s.player) 

in (select name,player from music group by name,player having count(*) > 1)  and rowid not in (select min(rowid) from music group by name,player having count(*)>1)

网上搜的最多的就是这个，但是是错误的，搞不懂为啥是错误的还这么多人传·

...全文

顶刊级科研绘图技巧顶刊级科研绘图技巧阅读详情

cwjieNo1

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

cwjieNo1 2011-05-07

打赏
举报

我是要删除多余的记录，有ID 为主键，一条表记录，如果name,player 相同，就认为这条记录有重复·
删除后剩下一条记录

cwjieNo1 2011-05-07

打赏
举报

[Quote=引用 3 楼 tangdunfeng 的回复:]
SQL code

delete table1 where id not in (
select min(id),name,player from table1 group by name,player
)
[/Quote]

这个报错？

Billy 2011-05-07

打赏
举报

这个要看是表有无主键ID，重复是完全重复还是部分重复,

ex1:无主键（设计不合理)，记录完全重复
set xact_abort on
begin tran
select distinct * into #temp from music
drop table music
select * into music from #temp
drop table #temp
commit tran

zhujiebo 2011-05-07

打赏
举报

用distinct来区别开重复的数据

sankunpt1 2011-05-07

打赏
举报

SELECT DISTINCT name,player
FROM music;

cwjieNo1 2011-05-07

打赏
举报

以 name ，player 来区分不同的记录·

Sweet-Tang 2011-05-07

打赏
举报



delete table1 where id not in (

select min(id),name,player from table1 group by name,player 

)

快溜 2011-05-07

打赏
举报

select * from music m 

where checksum(m.name , m.player) in 

(select checksum(name ,player) from music group by name, player having count(*)>1)

Rotel-刘志东 2011-05-07

打赏
举报

---查询语句

select name,player from music 

where not exists (select 1 from music a where name=a.name and id<a.id)

---删除语句

delete from music

where where not exists (select 1 from music a where name=a.name and id<a.id)

linmeiyang 2011-05-07

打赏
举报

用distinct

Billy 2011-05-07

打赏
举报

[Quote=引用 12 楼 cwjieno1 的回复:]
我是在sql 里面·
delete music where id not in (
select min(id),name,player from music group by name,player
)

报错：当没有用 EXISTS 引入子查询时，在选择列表中只能指定一个表达式。
[/Quote]

delete music where id not in (

select min(id) as id from music group by name,player 

)

--小F-- 2011-05-07

打赏
举报

delete music where id not in (select id from(

select min(id),name,player from music group by name,player )t 

)

cwjieNo1 2011-05-07

打赏
举报

我是在sql 里面·
delete music where id not in (
select min(id),name,player from music group by name,player
)

报错：当没有用 EXISTS 引入子查询时，在选择列表中只能指定一个表达式。

Sweet-Tang 2011-05-07

打赏
举报

delete music where id not in (

select min(id),name,player from music group by name,player 

)

--小F-- 2011-05-07

打赏
举报

[Quote=引用楼主 cwjieno1 的回复:]
弱弱问下;表 music 里面有name,player 等字段，有好些记录是重复的
如何查找出这些多余的重复记录呢？
如何删除这些，重复记录中只保留一条？？

SQL code

select * from music m
where (m.name , m.player) in
(select name ,player from music group by name, pl……
[/Quote]
你这个在ORACLE里面是可以用的

sql 查询、 删除表中重复记录

相关推荐

sql 查询、删除表中重复记录