求一条查找重复数据的语句
id nid name
1 1001 1
2 1002 1
3 1003 1
4 1004 1
5 1101 1
查nid前3位一样并且name一样的的数据
[解决办法]
declare @t table(id int,nid varchar(10),name int)
insert into @t
select 1, '1001 ',1
union all select 2, '1002 ',1
union all select 3, '1003 ',1
union all select 4, '1004 ',1
union all select 5, '1101 ',1
select * from @t a
where exists(select 1 from @t b where a.name=b.name and left(b.nid,3)=left(a.nid,3) group by left(b.nid,3) having count(*)> =2)
/*
id nid name
----------- ---------- -----------
1 1001 1
2 1002 1
3 1003 1
4 1004 1
（所影响的行数为 4 行）
*/
[解决办法]
create table t(id int,nid varchar(10),name int)
insert into t
select 1, '1001 ',1
union all select 2, '1002 ',1
union all select 3, '1003 ',1
union all select 4, '1004 ',1
union all select 4, '1004 ',1
union all select 5, '1101 ',1
create view tt
as
select * from t a
where exists(select 1 from t where left(a.nid,3)=left(nid,3) and a.name=name
group by left(nid,3) having count(1)> 1 )
select * from tt a
where exists(select 1 from tt where right(a.nid,1)=right(nid,1) and a.name=name
group by right(nid,1) having count(1)=1)
id nid name
----------- ---------- -----------
1 1001 1
2 1002 1
3 1003 1
(3 行受影响)
