--要求，name或tel有一个重复，则就算是重复的，取重复记录最大的id列表
if object_id('t') is not null drop table t
create table t(
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');
/*
这个地方你来，先谢了，呵呵。。。
*/
--结果
id
3
6
7
------解决方案--------------------
with table as
(
select ID, rownumber = ROW_NUMBER()OVER(PARTITION BY t.id ORDER BY t.id desc)
from t
)
select ID
from table
where rownumber = 1
------解决方案--------------------
上面的不对，用这个
with tabletmp as
(
select max(ID) as ID_Temp, name
from t
-- order by max(ID), name
group by name
)
select ID_Temp
from tabletmp
order by ID_Temp
------解决方案--------------------
这个肯定也不对，呵呵
------解决方案--------------------
if object_id('t') is not null drop table t
create table t(
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');
select
*,
name的重复数=(select count(1) from t where name=m.name),
tel的重复数=(select count(1) from t where tel=m.tel)
from t m
/*
id name tel name的重复数 tel的重复数
----------- ---------- ---------- ----------- -----------
1 zhang 11111 3 2
2 zhang 11111 3 2
3 zhang 22222 3 1
4 test 33333 2 2
5 test 12345 2 1