SQL server 2008,有列：姓名、电话，电话有重复行，想在原表中只保留重复行的第一行，求语句

[oiph]

表名BM
电话 姓名
13300001111 张三
13300001111 李四
13300001111 鬼子
18877776666 老先生

当表中电话有重复行，而对应的姓名不同，想在原表中只保留重复行的第一行，变成以下效果
电话 姓名
13300001111 张三
18877776666 老先生

求语句，谢谢

[水族杰纶]

;WITH TMP AS (SELECT *,RN=ROW_NUMBER()OVER(PARTITION BY 电话 ORDER BY GETDATE()) FROM TB) --QUERY SELECT * FROM TMP WHERE RN=1 --DELETE DELETE TMP WHERE RN!=1

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-11-12 14:56:32
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[BM]
if object_id('[BM]') is not null drop table [BM]
go 
create table [BM]([电话] bigint,[姓名] varchar(6))
insert [BM]
select 13300001111,'张三' union ALL
select 13300001111,'李四' union all
select 13300001111,'鬼子' union all
select 18877776666,'老先生'
--------------开始查询--------------------------
SELECT 电话        ,           姓名 
FROM (
select *,ROW_NUMBER()OVER(PARTITION BY 电话 ORDER BY GETDATE())id
from [BM]a
)a
WHERE id=1
----------------结果----------------------------
/* 
电话                   姓名
-------------------- ------
13300001111          张三
18877776666          老先生
*/

[小魚人]

樓上的大神們寫的真快

[开着拖拉机泡妞]

----------------------------------------------------------------
-- Author  :TravyLee(走自己的路，让狗去叫吧！)
-- Date    :2013-11-12 15:17:30
-- Version:
--      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--	Jul  9 2008 14:43:34 
--	Copyright (c) 1988-2008 Microsoft Corporation
--	Developer Edition on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：#BM
if object_id('tempdb.dbo.#BM') is not null 
drop table #BM
go 
create table #BM([电话] bigint,[姓名] varchar(6))
insert #BM
select 13300001111,'张三' union all
select 13300001111,'李四' union all
select 13300001111,'鬼子' union all
select 18877776666,'老先生'
go

select 
	* from #BM a
where a.姓名=(select top 1 姓名 from #BM b where a.电话=b.电话 )
/*
电话	姓名
----   -----  ----  ----
13300001111	张三
18877776666	老先生
*/

[LongRui888]

是这样吗：

if object_id('tb') is not null 
drop table tb
go 

create table tb([电话] bigint,[姓名] varchar(6))

insert tb
select 13300001111,'张三' union all
select 13300001111,'李四' union all
select 13300001111,'鬼子' union all
select 18877776666,'老先生'
go


;with t
as
(
select *,
       ROW_NUMBER() over(partition by [电话]
                             order by @@servername) as rownum
from tb
)

--删除多余的数据
delete from t where rownum > 1



--查询数据，发现多余数据已经删除
select *
from tb
/*
电话	         姓名
13300001111	张三
18877776666	老先生
*/