偶弄出了个比较笨的方法,由于不知道3个字段或者4个字段为空的情况下需不需要删除,如果不需要你再改一下吧。WITH T AS( SELECT '1' A,'2' B,'3' C,'4' D,'5' E FROM dual UNION ALL SELECT '21', '2', '3', '4', '5' FROM dual UNION ALL SELECT '1', '2', '3', NULL, NULL FROM dual UNION ALL SELECT '1', '2', NULL, NULL, NULL FROM dual UNION ALL SELECT '2', '1', '3', '4', '5' FROM dual UNION ALL SELECT '2', '1', '3', '4', NULL FROM dual )SELECT * FROM (SELECT '@'||RTRIM(NVL2(A,A,'')||'@'||NVL2(B,B,'')||'@'||NVL2(C,C,'')||'@'||NVL2(D,D,'')||'@'||NVL2(E,E,''),'@')||'@' ALLSTR,A,B,C,D,E FROM T) T1, (SELECT '@'||RTRIM(NVL2(A,A,'')||'@'||NVL2(B,B,'')||'@'||NVL2(C,C,'')||'@'||NVL2(D,D,'')||'@'||NVL2(E,E,''),'@')||'@' ALLSTR FROM T) T2 WHERE T1.ALLSTR <> T2.ALLSTR AND INSTR(T2.ALLSTR,T1.ALLSTR) = 1 ;
7楼的代码有点小问题,不支持1, null, 3, null, null格式的数据,我改进了代码,不过代码中使用了正则表达式,需要oracle 10g以上版本才支持.WITH T AS( SELECT '1' A,'2' B,'3' C,'4' D,'5' E FROM dual UNION ALL SELECT '21', '2', '3', '4', '5' FROM dual UNION ALL SELECT '1', '2', '3', NULL, NULL FROM dual UNION ALL SELECT '1', '2', NULL, NULL, NULL FROM dual UNION ALL SELECT '1', NULL, NULL, '4', NULL FROM dual UNION ALL SELECT '2', '1', '3', '4', '5' FROM dual UNION ALL SELECT '2', '1', '3', '4', NULL FROM dual )SELECT * FROM (SELECT '@'||RTRIM(NVL2(A,A,'(.*)')||'@'||NVL2(B,B,'(.*)')||'@'||NVL2(C,C,'(.*)')||'@'||NVL2(D,D,'(.*)')||'@'||NVL2(E,E,'(.*)'),'@')||'@' ALLSTR,A,B,C,D,E FROM T) T1, (SELECT '@'||RTRIM(NVL2(A,A,'')||'@'||NVL2(B,B,'')||'@'||NVL2(C,C,'')||'@'||NVL2(D,D,'')||'@'||NVL2(E,E,''),'@')||'@' ALLSTR FROM T) T2 WHERE T1.ALLSTR <> T2.ALLSTR AND REGEXP_LIKE(T2.ALLSTR,T1.ALLSTR) ;
SELECT '1' A,'2' B,'3' C,'4' D,'5' E FROM dual UNION ALL
SELECT '21', '2', '3', '4', '5' FROM dual UNION ALL
SELECT '1', '2', '3', NULL, NULL FROM dual UNION ALL
SELECT '1', '2', NULL, NULL, NULL FROM dual UNION ALL
SELECT '2', '1', '3', '4', '5' FROM dual UNION ALL
SELECT '2', '1', '3', '4', NULL FROM dual
)SELECT * FROM
(SELECT '@'||RTRIM(NVL2(A,A,'')||'@'||NVL2(B,B,'')||'@'||NVL2(C,C,'')||'@'||NVL2(D,D,'')||'@'||NVL2(E,E,''),'@')||'@' ALLSTR,A,B,C,D,E FROM T) T1,
(SELECT '@'||RTRIM(NVL2(A,A,'')||'@'||NVL2(B,B,'')||'@'||NVL2(C,C,'')||'@'||NVL2(D,D,'')||'@'||NVL2(E,E,''),'@')||'@' ALLSTR FROM T) T2
WHERE T1.ALLSTR <> T2.ALLSTR AND
INSTR(T2.ALLSTR,T1.ALLSTR) = 1 ;
SELECT '1' A,'2' B,'3' C,'4' D,'5' E FROM dual UNION ALL
SELECT '21', '2', '3', '4', '5' FROM dual UNION ALL
SELECT '1', '2', '3', NULL, NULL FROM dual UNION ALL
SELECT '1', '2', NULL, NULL, NULL FROM dual UNION ALL
SELECT '1', NULL, NULL, '4', NULL FROM dual UNION ALL
SELECT '2', '1', '3', '4', '5' FROM dual UNION ALL
SELECT '2', '1', '3', '4', NULL FROM dual
)SELECT * FROM
(SELECT '@'||RTRIM(NVL2(A,A,'(.*)')||'@'||NVL2(B,B,'(.*)')||'@'||NVL2(C,C,'(.*)')||'@'||NVL2(D,D,'(.*)')||'@'||NVL2(E,E,'(.*)'),'@')||'@' ALLSTR,A,B,C,D,E FROM T) T1,
(SELECT '@'||RTRIM(NVL2(A,A,'')||'@'||NVL2(B,B,'')||'@'||NVL2(C,C,'')||'@'||NVL2(D,D,'')||'@'||NVL2(E,E,''),'@')||'@' ALLSTR FROM T) T2
WHERE T1.ALLSTR <> T2.ALLSTR AND
REGEXP_LIKE(T2.ALLSTR,T1.ALLSTR) ;
create table INPUT_USER_INFO
(
ID NUMBER not null,
FIRSTNAME VARCHAR2(30),
CERTNO VARCHAR2(30),
NATIONALITY VARCHAR2(50),
GENDER VARCHAR2(4),
STATION VARCHAR2(100),
CR_STANDARD VARCHAR2(20),
FLTNO VARCHAR2(20),
GOTO_TYPE VARCHAR2(20),
LASTNAME VARCHAR2(30),
START_TIME VARCHAR2(20),
ARRIVE_TIME VARCHAR2(20),
GROUPNAME VARCHAR2(100),
CUS_TYPE VARCHAR2(100),
TRAFFICLEVEL VARCHAR2(20),
RES_UNIT VARCHAR2(100),
RESTADDRESS VARCHAR2(100),
NORMALBAG VARCHAR2(4),
ILLEGIMATEBAG VARCHAR2(100),
START_CITY VARCHAR2(50),
ARRIVE_CITY VARCHAR2(50)
)上面是表字段,要求表中的每个certno(证件号)可以存在两条数据,这两条数据除了出发城市(start_city)和到达城市(arrive_city)是反向的,其他字段都是相同的。但是现在表中一个证件号存在多条数据,其中有很多为空的字段,现在要筛选把多余的冗余数据删除,只留下符合要求的数据。