table: A B 1 1 2 1 3 1 1 2 2 2 4 2 a c a d c p d q要在table中根据A字段出有重复的记录: 1 1 2 1 1 2 2 2 a c a d
create table t(a varchar(01),b varchar(01)) insert into t select '1','1' insert into t select '2','1' insert into t select '3','1' insert into t select '1','2' insert into t select '2','2' insert into t select '4','2' insert into t select 'a','c' insert into t select 'a','d' insert into t select 'c','p' insert into t select 'd','q'select * from T a where exists(select 1 from t where A = a.A group by A having count(*) > 1 )drop table t a b ---- ---- 1 1 2 1 1 2 2 2 a c a d
select * from a where A in (select A from a where A = a.A group by A having count(*) > 1 )改成这个就OK了
select * from a T where exists(select 1 from a where A = T.A group by A having count(*) > 1 )测试为表的别名:T的问题 (select 1 from a where A = T.A group by A having count(*) > 1 ) 从表名为a中取数据,取与别名为T的表字段相等的数据
select * from t where a in( select a from t group by a having count(b)>1)
select * from table a where A in (select A from table a group by A having count(*) > 1 )
A B
1 1
2 1
3 1
1 2
2 2
4 2
a c
a d
c p
d q要在table中根据A字段出有重复的记录:
1 1
2 1
1 2
2 2
a c
a d
insert into t select '1','1'
insert into t select '2','1'
insert into t select '3','1'
insert into t select '1','2'
insert into t select '2','2'
insert into t select '4','2'
insert into t select 'a','c'
insert into t select 'a','d'
insert into t select 'c','p'
insert into t select 'd','q'select * from T a
where exists(select 1 from t where A = a.A group by A having count(*) > 1 )drop table t
a b
---- ----
1 1
2 1
1 2
2 2
a c
a d
where A in (select A from a where A = a.A group by A having count(*) > 1 )改成这个就OK了
where exists(select 1 from a where A = T.A group by A having count(*) > 1 )测试为表的别名:T的问题
(select 1 from a where A = T.A group by A having count(*) > 1 )
从表名为a中取数据,取与别名为T的表字段相等的数据
select a from t group by a
having count(b)>1)
where A in (select A from table a group by A having count(*) > 1 )