删除一张表中重复记录的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!
解决方案 »
- Parameter 'table_name' not found in the collection. C#中调用MySQL存储过程出错
- 请教:如何更改server.xml文件的配置啊?急
- 高分求一个mysql自定义函数,实现“返回给定字符串的utf16编码值”功能,
- 在Extjs页面的文本框中按回车mysql出现乱码
- 数据库查询效率
- mysql存储过程参数可不可以带默认值
- 现在用sql server2000,现在想学mysql,问简单几个问题,请大家指教.
- mysql語句的一個問題????
- 主从备份后,部分语句无法在从库同步执行
- 这个死锁原因是什么?
- 高手请进,求一句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)