sql 根据某一字段查询重复记录,同时要查询出所有满足条件的字段信息?
怎么写啊 比如有一张表A:有字段ID,Name,Time,Pass等(ID为主键)
Name相同的记录可能有很多条,这时只根据相同Name的用户的所有信息
如果Name只有一条记录,则不查询出该记录 请问这条sql怎么实现啊
怎么写啊 比如有一张表A:有字段ID,Name,Time,Pass等(ID为主键)
Name相同的记录可能有很多条,这时只根据相同Name的用户的所有信息
如果Name只有一条记录,则不查询出该记录 请问这条sql怎么实现啊
select * from a
where a.rowid<>(
select max(a1.rowid)
from a a1
where a.name=a1.name)
where exists(select 1 from A where name=a0.name and rowid<>a0.rowid)
from A a
where rowid!=(select max(b.rowid)
from A b
where a.name=b.name);
from
(select id,time,pass,name,
row_number() over(partition by id order by name) as ranknum
from a
)
where ranknum<>1
(id number,
name varchar2(20),
time date,
pass varchar2(20),
constraint pk_a_id primary key (id));insert into a values(1,'tiantom',to_date('2009-1-1','yyyy-mm-dd'),'123');
insert into a values(2,'tiantom',to_date('2009-1-2','yyyy-mm-dd'),'124');
insert into a values(3,'tomtian',to_date('2009-1-1','yyyy-mm-dd'),'132');
insert into a values(4,'tomtian',to_date('2009-1-5','yyyy-mm-dd'),'135');
insert into a values(5,'tomtian',to_date('2009-1-7','yyyy-mm-dd'),'138');
insert into a values(6,'tomtian',to_date('2009-1-10','yyyy-mm-dd'),'152');
insert into a values(7,'tom',to_date('2009-3-1','yyyy-mm-dd'),'137');
insert into a values(8,'tian',to_date('2009-4-1','yyyy-mm-dd'),'190');
insert into a values(9,'jim',to_date('2009-3-20','yyyy-mm-dd'),'136');
insert into a values(10,'mary',to_date('2009-2-21','yyyy-mm-dd'),'156');
commit;
select * from a;
select * from A where name in
(select name from A group by name having count(*) >1);
select id,name,time,password from test_A a where a.rowid<>(select max(rowid) from test_A where name=a.name);
select id,name,time,password from test_A a where exists(select 1 from test_A where name=a.name and rowid<>a.rowid);
这三条我分别测试了是可以的
where a.rowid <>(
select max(a1.rowid)
from a a1
where a.name=a1.name)
select id,name,time,password from test_A where name in(select name from test_A group by name having count(name)>1);
select id,name,time,password from test_A a where exists(select 1 from test_A where name=a.name and rowid <>a.rowid);