表T1里的内容如下
A B
abc Test1
abd Test2
abc Test4
acd Test5
abe Test6
abd Test7
abc Test8
ace Test9要求查询出下面的内容(也就是A字段出现多次的将显示出来,只出现一次的不显示),请问sql语句怎么写?
A B
abc Test1
abc Test4
abc Test8
abd Test2
abd Test7
A B
abc Test1
abd Test2
abc Test4
acd Test5
abe Test6
abd Test7
abc Test8
ace Test9要求查询出下面的内容(也就是A字段出现多次的将显示出来,只出现一次的不显示),请问sql语句怎么写?
A B
abc Test1
abc Test4
abc Test8
abd Test2
abd Test7
go
create table tb
(
A varchar(10),
B varchar(10)
)
insert into tb values('abc', 'Test1')
insert into tb values('abd', 'Test2')insert into tb values('abc', 'Test4')
insert into tb values('acd', 'Test5')
insert into tb values('abe', 'Test6')
insert into tb values('abd', 'Test7')
insert into tb values('abc', 'Test8')
insert into tb values('ace', 'Test9')select * from tb where A in (select A from tb group by A having count(1) >1 )
/*
A B
---------- ----------
abc Test1
abd Test2
abc Test4
abd Test7
abc Test8(5 行受影响)
go
create table tb
(
A varchar(10),
B varchar(10)
)
insert into tb values('abc', 'Test1')
insert into tb values('abd', 'Test2')insert into tb values('abc', 'Test4')
insert into tb values('acd', 'Test5')
insert into tb values('abe', 'Test6')
insert into tb values('abd', 'Test7')
insert into tb values('abc', 'Test8')
insert into tb values('ace', 'Test9')select * from tb where A in (select A from tb group by A having count(1) >1 )
/*
A B
---------- ----------
abc Test1
abd Test2
abc Test4
abd Test7
abc Test8(5 行受影响)
where A in
(
select A
from A
group by A
having count(A)>1
)
多谢,不过要求是下面这样的呢,就是相同A列值要连续而不是分开的,要怎么查?
abc Test1
abc Test4
abc Test8
abd Test2
abd Test7
order by A
/*
A B
---------- ----------
abc Test1
abc Test4
abc Test8
abd Test7
abd Test2(5 行受影响)
create table T1
(A varchar(10),B varchar(10))
insert into T1
select 'abc','Test1'
union all
select 'abd','Test2'
union all
select 'abc','Test4'
union all
select 'acd','Test5'
union all
select 'abe','Test6'
union all
select 'abd','Test7'
union all
select 'abc','Test8'
union all
select 'ace','Test9'
;with cte1 as
(
select ROW_NUMBER() over(partition by A order by B) as id,* from T1
)
,cte2 as
(
select COUNT(id) id,A from cte1 group by A having COUNT(id)>1
)select * from T1 where a in(select a from cte2)