最终结果帖，非答案请勿回，毕竟大家时间保贵，谢谢！

[人生无悔]

有兴趣请到此地址回帖，谢谢！
http://topic.csdn.net/u/20120813/19/2e35af97-6a4d-4dfe-ae06-bd1c05568299.html?88227

结果：3,6,7 【这种是指重复的取最大的，没重复的保留】
3,7都可以【这种就是取最大的含重复的id】


没办法，只能发200分的，因此发两个吧，此帖最终答案帖，谢谢！

[叶子]

-- =============================================
-- Author:		<maco_wang>
-- Create date: <2012-08-13>
-- Description:	<抛砖引玉，代码凌乱，望见谅>
-- 这个递归貌似用cte表达式理论上绝对可以的，但是我没写成功
-- =============================================
create proc zhangandli_t
as
begin
	--创建个临时表
	create table #t 
	(id int ,name varchar(8),tel varchar(6),groupno int default 0)
	--把数据插入临时表
	insert into #t(id,name,tel) select * from t
	--定义组号
	declare @groupno int set @groupno=1
	--定义两个变量
	declare @name varchar(8),@tel varchar(8)
	--定义每组的起始序号
	declare @i int

	maco:
	select @i=min(id) from #t where groupno=0
	update #t set groupno=@groupno where id=@i
	select @name=name from #t where groupno=@groupno
	update #t set groupno=@groupno where name=@name

	first:
	while((select count(1) from #t where groupno=0 
		   and tel in (select tel from #t where groupno=@groupno))>0)
	begin
		update #t set groupno=@groupno
		where groupno=0 
			  and tel in (select tel from #t where groupno=@groupno)
		goto second
	end

	second:
	while((select count(1) from #t where groupno=0 
		   and name in (select name from #t where groupno=@groupno))>0)
	begin
		update #t set groupno=@groupno
		where groupno=0 
			  and name in (select name from #t where groupno=@groupno)
		goto first
	end

	--查看是否有没有编组的
	if(exists(select 1 from #t where groupno=0))
	begin
		set @groupno=@groupno+1
		goto maco
	end

	select * from #t
	/*
	id          name     tel    groupno
	----------- -------- ------ -----------
	1           zhang    11111  1
	2           zhang    11111  1
	3           zhang    22222  1
	4           test     33333  2
	5           test     12345  2
	6           test1    55555  3
	7           test3    33333  2
	*/
	--查看重复数据的最大值
	select max(id) as id from #t group by groupno having(count(1)>1)
	/*
	id
	-----------
	3
	7
	*/
	--查看重复数据的最大值
	select max(id) as id from #t group by groupno
	/*
	id
	-----------
	3
	7
	6
	*/

	--删除临时表
	drop table #t
end

go
exec zhangandli_t

[人生无悔]

如果有其他较简单的方式，请在此帮贴出，最终采纳后，会另开帖给分的，先谢了！

[筱筱澄]

--3/6/7
select * from t as a
where not exists(select 1 from t as b where b.name=a.name and b.tel>a.tel)
and not exists(select 1 from t as c where c.tel=a.tel and c.name>a.name)

--3/7
select * from t as a
where  exists(select 1 from t as b where (b.name=a.name and b.tel<a.tel) or( b.tel=a.tel and b.name<a.name))
and not exists(select 1 from t as c where c.tel=a.tel and c.name>a.name)

可能数据不是很多在，这个不确定是对的

[叶子]

当数据多了的时候，他是无限级判断的

declare @name varchar(8)
set @name='test'

;with maco as
(
	select id,name,tel from t where name=@name
	union all
	select a.* from t a ,maco b where a.tel=b.tel 
	and a.id not in (select b.id from b)
	union all
	select a.* from t a ,maco b where a.name=b.name 
	and a.id not in (select b.id from b)
)
select * from maco

with 表达式不支持内部子查询？

[筱筱澄]

[Quote=引用 4 楼 的回复:]
当数据多了的时候，他是无限级判断的
SQL code

declare @name varchar(8)
set @name='test'

;with maco as
(
select id,name,tel from t where name=@name
union all
select a.* from t a ,maco b where a.tel=b.tel ……
[/Quote]
你这个b从何来啊，这个跟cte 没毛线关系

另外cte 不支持多个递归引用。

[叶子]

--大版V5
;with a as
(
	select a.*,b.id bid from t a
    inner join t b 
    on (a.name = b.name or a.tel = b.tel) and a.id<>b.id
)

select distinct id from a a2 
where not exists(select 1 from a 
                 where (a.name = a2.name or a.tel = a2.tel)  and a.bid>a2.id)
/*
id
-----------
3
7
*/

[问苍天人生几何]

[code=SQL]select max(tt.id) 结果 from
(
select a.*
from t a, t b
where (a.name = b.name or a.tel = b.tel) and a.id<>b.id
) tt
group by tt.name
order by max(tt.id)[code]

[zhazhuzhao]

怎么我写的那么复杂：

with nameg as(
	--name group
	select tcount.*,ng.namegroup from 
	(
	select t.id,t.name,t.tel,row_number() over (partition by name order by id desc) as namenum
	from t) as tcount
	join 
	(
	select distinct name,row_number() over (order by name) as namegroup
	from t
	group by name
	) as ng on tcount.name=ng.name
),
telg as(
--tel group
	select telcount.*,ng.telgroup from 
	(
	select id,name,tel,row_number() over (partition by tel order by id desc) as telnum 
	from t
	) as telcount
	join 
	(
	select distinct tel,row_number() over (order by tel) as telgroup
	from t
	group by tel
	) as ng on telcount.tel=ng.tel
),
 
mergeG as 
(
--merge
select nameg.*,telg.telnum,telg.telgroup from 
nameg
join telg on  nameg.id=telg.id
)



--select max(id) as gid from 
--(
--select mergeG.*,
--case when tempTelGroup.mergegroup is null then mergeG.namegroup 
--	 else tempTelGroup.mergegroup
--end as CheckGroup

--from mergeG
--left join 
--(
----Get telCountnum
--select  telg.telgroup,MIN(mergeG.namegroup) as mergegroup
--from mergeG
--join telg on mergeG.telgroup=telg.telgroup and (mergeG.id!=telg.id)
--group by telg.telgroup) as tempTelGroup on mergeG.telgroup=tempTelGroup.telgroup

--) as t
--group by CheckGroup

select mergeG.*,
case when tempTelGroup.mergegroup is null then mergeG.namegroup 
	 else tempTelGroup.mergegroup
end as CheckGroup

from mergeG
left join 
(
--Get telCountnum
select  telg.telgroup,MIN(mergeG.namegroup) as mergegroup
from mergeG
join telg on mergeG.telgroup=telg.telgroup and (mergeG.id!=telg.id)
group by telg.telgroup) as tempTelGroup on mergeG.telgroup=tempTelGroup.telgroup
order by CheckGroup,id desc

[人生无悔]

[Quote=引用 3 楼 的回复:]
SQL code
--3/6/7
select * from t as a
where not exists(select 1 from t as b where b.name=a.name and b.tel>a.tel)
and not exists(select 1 from t as c where c.tel=a.tel and c.name>a.name)

--3/7
……
[/Quote]
谢谢美女！
不过这个不正确，举例
truncate table t;
insert into t values(1,'zhang','11111');
insert into t values(2,'zhang','22222');--2,3两条的电话换个顺序就不正确了
insert into t values(3,'zhang','11111');
insert into t values(4,'test','33333');
insert into t values(5,'test','12345');
insert into t values(6,'test1','55555');
insert into t values(7,'test3','33333');
结果应是不变的，呵呵

[人生无悔]

[Quote=引用 4 楼 的回复:][/Quote]
支持子查询，但不支持cte本身作为子查询，应比我清楚，呵呵，谢谢！

[人生无悔]

[Quote=引用 6 楼 的回复:][/Quote]
大版，怎一个强字了得，差点没看懂，呵呵，谢谢！

[中国风]

这需要递归实现

DECLARE @t table (
id int,
name varchar(10),
tel varchar(10)
)
insert into @t values(1,'zhang','11111');
insert into @t values(2,'zhang','11111');
insert into @t values(3,'zhang','22222');
insert into @t values(4,'test','33333');
insert into @t values(5,'test','12345');
insert into @t values(6,'test1','55555');
insert into @t values(7,'test3','33333');



	
;WITH a
AS
(
SELECT 
	a.*,b.ID AS ID2
FROM @t AS a
	INNER JOIN @t AS b ON (a.name = b.name or a.tel = b.tel) AND a.ID<>b.ID
),b
AS
(
SELECT id,NAME,tel FROM a  AS a2 WHERE NOT EXISTS(SELECT 1 FROM a WHERE (a.name = a2.name or a.tel = a2.tel)  AND a.ID2>a2.ID)
)
SELECT * FROM b
UNION 
SELECT * FROM @t AS a2 WHERE NOT EXISTS(SELECT 1 FROM a WHERE id=a2.ID)

/*
id	NAME	tel
3	zhang	22222
6	test1	55555
7	test3	33333
*/

[中国风]

insert into @t values(8,'test3','44444'); --新增一条记录

DECLARE @t table (
id int,
name varchar(10),
tel varchar(10)
)
insert into @t values(1,'zhang','11111');
insert into @t values(2,'zhang','11111');
insert into @t values(3,'zhang','22222');
insert into @t values(4,'test','33333');
insert into @t values(5,'test','12345');
insert into @t values(6,'test1','55555');
insert into @t values(7,'test3','33333');

insert into @t values(8,'test3','44444'); --新增一条记录



	
;WITH a
AS
(
SELECT 
	a.*,b.ID AS ID2
FROM @t AS a
	INNER JOIN @t AS b ON (a.name = b.name or a.tel = b.tel) AND a.ID<>b.ID
),b
AS
(
SELECT id,NAME,tel FROM a  AS a2 WHERE NOT EXISTS(SELECT 1 FROM a WHERE (a.name = a2.name or a.tel = a2.tel)  AND a.ID2>a2.ID)
)
SELECT * FROM b
UNION 
SELECT * FROM @t AS a2 WHERE NOT EXISTS(SELECT 1 FROM a WHERE id=a2.ID)

/*
id	NAME	tel
3	zhang	22222
6	test1	55555
8	test3	44444
*/

[人生无悔]

多谢各位，公司封网了，唉，只能回家再看了，只有一台电脑可以上网，还只能上个十来分钟，还只有记事本可以用，郁闷呀，先谢了，呵呵