Oracle sql：[1]获取多列值相同数据行

1、创建实验模拟多行多列值数据相同的行

--创建表

create table CLGJXX

(

  ID   NUMBER(3) unique,

  HPHM VARCHAR2(5),

  JGSJ VARCHAR2(14)

)

--插入数据

insert into clgjxx (ID, HPHM, JGSJ) values (101, 'aaa', '20141010161000');

insert into clgjxx (ID, HPHM, JGSJ) values (102, 'aaa', '20141010161000');

insert into clgjxx (ID, HPHM, JGSJ) values (103, 'bbb', '20141010161001');

insert into clgjxx (ID, HPHM, JGSJ) values (104, 'bbb', '20141010161001');

insert into clgjxx (ID, HPHM, JGSJ) values (105, 'ccc', '20141010161100');

insert into clgjxx (ID, HPHM, JGSJ) values (106, 'ccc', '20141010161100');

insert into clgjxx (ID, HPHM, JGSJ) values (107, 'aaa', '20141010161124');

commit;

说明：CLGJXX 表中出现了多行HPHM 列和JGSJ 列的值相同

2、获取所有多行多列值数据相同的行

with same_date as

(

select hphm,jgsj from clgjxx group by (hphm,jgsj) having count(1) >1

)

select * from clgjxx a where exists

(

select 'A' from same_date b

where a.hphm=b.hphm

and a.jgsj=b.jgsj

)；

3、获取多行多列值数据相同的行中的某一行，这在删除的时候有用

select * FROM clgjxx a

WHERE a.ROWID >

(

SELECT MIN( b.ROWID ) FROM clgjxx b

WHERE b.hphm = a.hphm

and b.jgsj=a.jgsj

);

4、创建模拟多行相同数据

create table CLGJXX_2

(

  ID   NUMBER(3) ,

  HPHM VARCHAR2(5),

  JGSJ VARCHAR2(14)

);

--插入数据

insert into CLGJXX_2 (ID, HPHM, JGSJ) values (101, 'aaa', '20141010161000');

insert into CLGJXX_2 (ID, HPHM, JGSJ) values (101, 'aaa', '20141010161000');'20141010161000');

insert into CLGJXX_2 (ID, HPHM, JGSJ) values (102, 'bbb', '20141010161001');

insert into CLGJXX_2 (ID, HPHM, JGSJ) values (102, 'bbb', '20141010161001');

insert into CLGJXX_2 (ID, HPHM, JGSJ) values (103, 'ccc', '20141010161100');

insert into CLGJXX_2 (ID, HPHM, JGSJ) values (103, 'ccc', '20141010161100');

insert into CLGJXX_2 (ID, HPHM, JGSJ) values (107, 'aaa', '20141010161124');

commit;

说明：CLGJXX 表中出现了多行值相同数据行

5、获取相同数据的行

with same_date as

(

select hphm,jgsj from clgjxx_2 group by (hphm,jgsj) having count(1) >1

)

select * from clgjxx_2 a where exists

(

select 'A' from same_date b

where a.hphm=b.hphm

and a.jgsj=b.jgsj

);

注意：在这里可以发现只是和第二步骤表名不同，其实这是有规律的，只要保证所选取的分组列分组之后的数据唯一就行

6、获取相同数据行的某一行

select * FROM clgjxx_2 a

WHERE a.ROWID >

(

SELECT MIN( b.ROWID ) FROM clgjxx_2 b

WHERE b.hphm = a.hphm

and b.jgsj=a.jgsj

);

注意：在这里可以发现只是和第四步骤表名不同，和上一步骤一样，只要保证where谓词分组后数据唯一