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

[cwjieNo1]

弱弱问下;表 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)  

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

[--小F--]

[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里面是可以用的

[Sweet-Tang]

delete music where id not in (
select min(id),name,player from music group by name,player 
)

[cwjieNo1]

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

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

[--小F--]

delete music where id not in (select id from(
select min(id),name,player from music group by name,player )t 
)

[Billy]

[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 
)

[linmeiyang]

用distinct

[Rotel-刘志东]

---查询语句
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)

[快溜]

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

[Sweet-Tang]

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

[cwjieNo1]

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

[sankunpt1]

SELECT DISTINCT name,player
FROM music;

[zhujiebo]

用distinct来区别开重复的数据

[Billy]

这个要看是表有无主键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

[cwjieNo1]

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

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

这个报错？

[cwjieNo1]

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