删除一张表中重复记录的SQL语句怎么写?
例如:
table T :
+----+-------+
| id | name |
+----+-------+
| 1 | fuck |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | hone |
| 6 | ho'n |
| 7 | ho'ne |
| 8 | zho"u |
| 9 | a |
| 10 | a |
| 11 | b |
+----+-------+
如何删除表中name字段有重复的记录?在这个例子中'a'和'b'有重复,即在id为9和10的记录保留一条既可,id为2和11的记录保留一条既可.
这个SQL语句怎么写?要求用一条语句。
thanks very much!
例如:
table T :
+----+-------+
| id | name |
+----+-------+
| 1 | fuck |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | hone |
| 6 | ho'n |
| 7 | ho'ne |
| 8 | zho"u |
| 9 | a |
| 10 | a |
| 11 | b |
+----+-------+
如何删除表中name字段有重复的记录?在这个例子中'a'和'b'有重复,即在id为9和10的记录保留一条既可,id为2和11的记录保留一条既可.
这个SQL语句怎么写?要求用一条语句。
thanks very much!
解决方案 »
- 纯菜,第一次接触,免费送小分...如何启用mysql客户端
- match函数的具体用法,得到的结果为0
- 谁能帮我解释一下这两条语句的效率为什么差这么多,select XX ... order by
- MySQL查询耗时过长问题
- 为什么where中出现了is null还能使用索引?
- c#对Mysql的备份
- 关于mysql中文显示为"?"的几个问题!在线等等~
- 抽风了,我把mysql.user表中的host字段的值改成*了咋办?
- mysql的"Incorrect key file for table '/tmp/#sql_1194_12.MYI'; try to repair it"问题
- 关于hibernate添加纪录的问题
- 高手请进,求一句MYSQL语句!!!
- 6张表关联,出现问题
select name,min(id) into newtt from tt group by name
(
id int,
name varchar(10)
)
insert into t(id,name)select 1,'fuck' union all
select 2,'b' union all
select 3,'c' union all
select 4,'d' union all
select 5,'hone' union all
select 6,'ho''n' union all
select 7,'ho''ne' union all
select 8,'zhou' union all
select 9,'a' union all
select 10,'a' union all
select 11,'b'delete t from t inner join
(
SELECT id from t where EXISTS (select 1 from t as tb where name=t.name and id <t.id)
) as tbr
on t.id = tbr.id
select * from t
-- result
1 fuck
2 b
3 c
4 d
5 hone
6 ho'n
7 ho'ne
8 zhou
9 a
delete T as a from T as a,
(
select *,min(id) from T group by name having count(*) > 1
) as b
where a.name = b.name and a.id > b.id;
delete T as a from T as a,
(
select *,min(id) from T group by name having count(1) > 1
) as b
where a.name = b.name and a.id > b.id;
delete from t where id not in (select distinct name from t)