日期:2014-05-17 浏览次数:20916 次
--要求,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
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