id a_id str
1 3 a
2 3 a
3 34 b
4 21 c
5 13 a
6 34 d
7 21 c
id是唯一的 a_id是重复的 str可能是重复的要求能查出 a_id相同,str相同的记录
---------------要求结果为
a_id str
3 a
3 a
21 c
21 c
1 3 a
2 3 a
3 34 b
4 21 c
5 13 a
6 34 d
7 21 c
id是唯一的 a_id是重复的 str可能是重复的要求能查出 a_id相同,str相同的记录
---------------要求结果为
a_id str
3 a
3 a
21 c
21 c
str
from tb group by a_id,str
having count(*)>=2
go
create table [tb] (id int,a_id int,str nvarchar(2))
insert into [tb]
select 1,3,'a' union all
select 2,3,'a' union all
select 3,34,'b' union all
select 4,21,'c' union all
select 5,13,'a' union all
select 6,34,'d' union all
select 7,21,'c'
select a_id,
str
from tb group by a_id,str
having count(*)>=2
/*
a_id str
----------- ----
3 a
21 c(2 個資料列受到影響)
*/
(select a_id , str from tb group by a_id , str having count(1) > 1) n
where m.a_id = n.a_id and m.str = n.str
order by m.a_id , m.str
insert into tb values(1 , 3 , 'a')
insert into tb values(2 , 3 , 'a')
insert into tb values(3 , 34 , 'b')
insert into tb values(4 , 21 , 'c')
insert into tb values(5 , 13 , 'a')
insert into tb values(6 , 34 , 'd')
insert into tb values(7 , 21 , 'c')
goselect m.a_id , m.str from tb m,
(select a_id , str from tb group by a_id , str having count(1) > 1) n
where m.a_id = n.a_id and m.str = n.str
order by m.a_id , m.strdrop table tb /*
a_id str
----------- ----------
3 a
3 a
21 c
21 c(所影响的行数为 4 行)
*/
go
create table [tb] (id int,a_id int,str nvarchar(2))
insert into [tb]
select 1,3,'a' union all
select 2,3,'a' union all
select 3,34,'b' union all
select 4,21,'c' union all
select 5,13,'a' union all
select 6,34,'d' union all
select 7,21,'c'
select a_id,
str
from tb t
where exists(select 1 from tb where a_id =t.a_id and str=t.str and id!=t.id)
/*
a_id str
----------- ----
3 a
3 a
21 c
21 c(4 個資料列受到影響)
*/