sql 2008,表TBB,有两列：姓名，电话，当两列完全相同时，在原表删除重复行，保留一行；当电话相同而姓名不同时，在原表中只保留最后一条记录，删除重复行。解决思路

select distinct * into #tb from tb
delete from tb --truncate
insert into tb select * from #tb
drop table #tb

------解决方案--------------------

SQL code

--为神马我的1，3楼，我看不见。。。
select id=identity(int,1,1),* 
into #t
from TBB

delete #t where exists(select 1 from #t as A where A.电话=#t.电话 and A.id>#t.id)

truncate table TBB

insert into TBB(姓名,电话)
select 姓名,电话 from #t

------解决方案--------------------

SQL code

----------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2012-05-25 16:31:53
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--    Apr 22 2011 11:57:00 
--    Copyright (c) Microsoft Corporation
--    Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([姓名] varchar(6),[电话] int)
insert [tb]
select '张三',5566777 union all
select '张三',5566777 union all
select '张三',5566777 union all
select '李四',8877542 union all
select '王小二',3305444 union all
select '兔子',8877542
--------------开始查询--------------------------
alter table tb add ID int identity--新增标识列
go
delete a from  tb a where  exists(select 1 from tb where 姓名=a.姓名 and 电话=a.电话 and ID>a.ID)--只保留一条记录
go
alter table tb drop column ID--删除标识列
 

select * from tb
----------------结果----------------------------
/* 姓名     电话
------ -----------
李四     8877542
兔子     8877542
王小二    3305444
张三     5566777

(4 行受影响)
*/

------解决方案--------------------

SQL code

----------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2012-05-25 16:31:53
-- Version:
--      Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86) 
--    Apr 22 2011 11:57:00 
--    Copyright (c) Microsoft Corporation
--    Enterprise Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------
--> 测试数据：[tb]
if object_id('[tb]') is not null drop table [tb]
go 
create table [tb]([姓名] varchar(6),[电话] int)
insert [tb]
select '张三',5566777 union all
select '张三',5566777 union all
select '张三',5566777 union all
select '李四',8877542 union all
select '王小二',3305444 union all
select '兔子',8877542
--------------开始查询--------------------------
alter table tb add ID int identity--新增标识列
go
delete a from  tb a where  exists(select 1 from tb where 电话=a.电话 and ID>a.ID)--只保留一条记录
go
alter table tb drop column ID--删除标识列
 

select * from tb
----------------结果----------------------------
/*姓名     电话
------ -----------
张三     5566777
王小二    3305444
兔子     8877542

(3 行受影响)

*/