sql查询语句

[zhantianyou]

Table tb: ColumnA ColumnB ColumnC
1 张三 45
2 李四 60
3 张三 75
4 王五 50
5 张三 55


请写一条删除语句，删除第一条之外的重复数据？
应该要怎么写?


delete from tb where ColumnA not in (select distinct ColumnA from tb order by ColumnA)

这样写对吗?

[开着拖拉机泡妞]

--> 测试数据：[test]
if object_id('[test]') is not null drop table [test]
create table [test](
[ColumnA] int,
[ColumnB] varchar(4),
[ColumnC] int
)
insert [test]
select 1,'张三',45 union all
select 2,'李四',60 union all
select 3,'张三',75 union all
select 4,'王五',50 union all
select 5,'张三',55

delete a from [test] a where  
exists(select 1 from [test] b where b.[ColumnB]=a.[ColumnB] and b.[ColumnA]<a.[ColumnA])

select * from test

/*
ColumnA	ColumnB	ColumnC
1	张三	45
2	李四	60
4	王五	50
*/

[Aaron_Chan]

create table #ttt(id int,[name] varchar(10),age int)
insert into #ttt select 6, '李四', 45
union all select   2, '李四', 60
union all select   3, '张三', 75
union all select   4, '王五' ,50
union all select   5, '张三' ,55

delete from #ttt where id not in(
select min(id) from #ttt t inner join (select [name],count(*) as total from #ttt group by [name])tmp on
t.[name]=tmp.[name] group by t.[name])

------------------------------------

id          name       age
----------- ---------- -----------
1           张三         45
2           李四         60
4           王五         50

(3 行受影响)

[Aaron_Chan]

------更正一下：
delete from #ttt where id not in(
select min(id) from #ttt  group by #ttt.[name])

--就这个简单的就可以了。