select a.* from table1 a,(select col1,col3 from table1 group by col1,col2 having count(0)>1) b where a.col1=b.col1 and a.col2=b.col2;
是不是要变成这个样子:table1 col1 col3 A X B X ???
select A.* from table1 A ,table1 B where (A.col1 = B.col1 and A.col3 =B.col3)
create table test( col1 varchar2(100), col2 varchar2(100), col3 varchar2(100) ); insert into test (col1,col2,col3) values ('A','1','X'); insert into test (col1,col2,col3) values ('A','2','X'); insert into test (col1,col2,col3) values ('B','1','Y'); insert into test (col1,col2,col3) values ('B','3','X'); commit;select * from test where (col1,col3)=(select col1,col3 from test group by col1,col3 having count(*)>1)result:A 1 X A 2 X
给你个好点的.但不知道你是不是要这个结果. SQL> Create Table test (col1 varchar2(10),col2 varchar2(10), col3 varchar2(10));表被创建SQL> SQL> Insert Into test Values('a','1','x');1 行 已插入SQL> Insert Into test Values('a','2','x');1 行 已插入SQL> Insert Into test Values('b','1','y');1 行 已插入SQL> Insert Into test Values('b','3','x');1 行 已插入SQL> SQL> Select a.* 2 From test a,test b 3 Where a.col1=b.col1 4 And a.col3=b.col3 5 And a.col2<>b.col2 6 ;COL1 COL2 COL3 ---------- ---------- ---------- a 2 x a 1 xSQL>
where a.col1=b.col1 and a.col2=b.col2;
col1 col3
A X
B X
???
where (A.col1 = B.col1 and A.col3 =B.col3)
col1 varchar2(100),
col2 varchar2(100),
col3 varchar2(100)
);
insert into test (col1,col2,col3) values ('A','1','X');
insert into test (col1,col2,col3) values ('A','2','X');
insert into test (col1,col2,col3) values ('B','1','Y');
insert into test (col1,col2,col3) values ('B','3','X');
commit;select * from test where (col1,col3)=(select col1,col3 from test group by col1,col3 having count(*)>1)result:A 1 X
A 2 X
SQL> Create Table test (col1 varchar2(10),col2 varchar2(10), col3 varchar2(10));表被创建SQL>
SQL> Insert Into test Values('a','1','x');1 行 已插入SQL> Insert Into test Values('a','2','x');1 行 已插入SQL> Insert Into test Values('b','1','y');1 行 已插入SQL> Insert Into test Values('b','3','x');1 行 已插入SQL>
SQL> Select a.*
2 From test a,test b
3 Where a.col1=b.col1
4 And a.col3=b.col3
5 And a.col2<>b.col2
6 ;COL1 COL2 COL3
---------- ---------- ----------
a 2 x
a 1 xSQL>