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 bwhere a.hphm=b.hphmand a.jgsj=b.jgsj)；

3、获取多行多列值数据相同的行中的某一行，这在删除的时候有用select * FROM clgjxx aWHERE a.ROWID >(SELECT MIN( b.ROWID ) FROM clgjxx bWHERE b.hphm = a.hphmand 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 bwhere a.hphm=b.hphmand a.jgsj=b.jgsj);注意：在这里可以发现只是和第二步骤表名不同，其实这是有规律的，只要保证所选取的分组列分组之后的数据唯一就行

6、获取相同数据行的某一行select * FROM clgjxx_2 aWHERE a.ROWID >(SELECT MIN( b.ROWID ) FROM clgjxx_2 bWHERE b.hphm = a.hphmand b.jgsj=a.jgsj);注意：在这里可以发现只是和第四步骤表名不同，和上一步骤一样，只要保证where谓词分组后数据唯一