求一条取重复记录的SQL语句
A B NAME
23 001
33 002
34 003
34 004
查出以下的结果
A+B 做为条件
只要记录中 A+B 的值 有重复的 就查出
但 单独的A重复或B重复则不查出来
如下
A B NAME
34 003
34 004
[解决办法]
create table T(A int, B int, Name char(3))
insert T select2,3, '001 '
union all select 3,3, '002 '
union all select 3,4, '003 '
union all select 3,4, '004 '
select a.* from T a
inner join
(
select A,B from T
group by A, B
having count(*)> 1
) b on a.A=b.A and a.B=b.B
--result
A B Name
----------- ----------- ----
3 4 003
3 4 004
(2 row(s) affected)
[解决办法]
create table tab
(
A varchar(10),
B varchar(20),
NAME varchar(20)
)
insert into tab select '2 ', '4 ', '10 '
insert into tab select '3 ', '4 ', '20 '
insert into tab select '3 ', '5 ', '40 '
insert into tab select '3 ', '5 ', '60 '
select a.*
from tab a ,(select A,B from tab group by A, B having count(1)> 1) b
where a.A=b.A and a.B=b.B
[解决办法]
select * from table t where exists(select 1 from table where name <> t.name and a+b=t.a+t.b)
[解决办法]
declare @ta table(A int, B int, NAME varchar(5))
insert @ta
select 2,3, '001 '
union all select 3,3, '002 '
union all select 3,4, '003 '
union all select 3,4, '004 '
select * from @ta a
where (select count(1) from @ta where a=a.a and b=a.b)> 1
（所影响的行数为 4 行）
A B NAME
----------- ----------- -----
3 4 003
3 4 004
（所影响的行数为 2 行）
[解决办法]
select * from table t where exists(select 1 from table where name <> t.name and a=t.a and b = t.b)
