因为之前的描述不清楚，重新发帖求助SQL。。。
PARENT CHILD ORG_PARENT GRP_CD US_TYPE START_NO END_NO START_SEQ END_SEQ LOCATION4700 2151 A17A 01 C 0 0 0 0 CN024700 2151 A17A 01 C 1 1 1 1 CN044700 2152 A17A 01 C 0 0 0 0 CN024700 2152 A17A 01 C 1 1 1 1 CN044700 2153 A17A 01 C 0 0 0 0 CN034700 2154 A17A 01 C 1 1 1 1 CN04 "希望查询出：PARENT、ORG_PARENT、GRP_CD、US_TYPE、START_NO、END_NO、START_SEQ、END_SEQ这8个字段的值相等时，child不相等，并且LOCATION 不相等的行" 如：结果应该是： PARENT CHILD ORG_PARENT GRP_CD US_TYPE START_NO END_NO START_SEQ END_SEQ LOCATION4700 2151 A17A 01 C 0 0 0 0 CN024700 2152 A17A 01 C 0 0 0 0 CN024700 2153 A17A 01 C 0 0 0 0 CN03 下面一组数据因为除了child不相同以外，其他字段的值都相等，所以不会被查询出来。 4700 2151 A17A 01 C 1 1 1 1 CN044700 2152 A17A 01 C 1 1 1 1 CN044700 2154 A17A 01 C 1 1 1 1 CN04
--测试表创建以及测试数据插入create table t_tableone(PARENT varchar2(10),CHILD varchar2(10),ORG_PARENT varchar2(10),GRP_CD varchar2(10),US_TYPE varchar2(10),START_NO varchar2(10),END_NO varchar2(10),START_SEQ varchar2(10),END_SEQ varchar2(10),LOCATION varchar2(10))insert into t_tableone select '4700','2151','A17A','01','C','0','0','0','0','CN02' from dual union allselect '4700','2151','A17A','01','C','1','1','1','1','CN04' from dual union allselect '4700','2152','A17A','01','C','0','0','0','0','CN02' from dual union allselect '4700','2152','A17A','01','C','1','1','1','1','CN04' from dual union allselect '4700','2153','A17A','01','C','0','0','0','0','CN03' from dual union allselect '4700','2154','A17A','01','C','1','1','1','1','CN04' from dual--查询语句select distinct t2.* from t_tableone t1, t_tableone t2where t1.PARENT=t2.PARENTand t1.ORG_PARENT=t2.ORG_PARENTand t1.GRP_CD=t2.GRP_CDand t1.US_TYPE=t2.US_TYPEand t1.START_NO=t2.START_NOand t1.END_NO=t2.END_NOand t1.START_SEQ=t2.START_SEQand t1.END_SEQ=t2.END_SEQand t1.child!=t2.childand t1.LOCATION!=t2.LOCATION--查询结果4700 2151 A17A 01 C 0 0 0 0 CN024700 2152 A17A 01 C 0 0 0 0 CN024700 2153 A17A 01 C 0 0 0 0 CN03
[解决办法]
--测试表创建以及测试数据插入create table t_tableone(PARENT varchar2(10),CHILD varchar2(10),ORG_PARENT varchar2(10),GRP_CD varchar2(10),US_TYPE varchar2(10),START_NO varchar2(10),END_NO varchar2(10),START_SEQ varchar2(10),END_SEQ varchar2(10),LOCATION varchar2(10))insert into t_tableone select '4700','2151','A17A','01','C','0','0','0','0','CN02' from dual union allselect '4700','2151','A17A','01','C','1','1','1','1','CN04' from dual union allselect '4700','2152','A17A','01','C','0','0','0','0','CN02' from dual union allselect '4700','2152','A17A','01','C','1','1','1','1','CN04' from dual union allselect '4700','2153','A17A','01','C','0','0','0','0','CN03' from dual union allselect '4700','2154','A17A','01','C','1','1','1','1','CN04' from dual--查询语句select distinct t2.* from t_tableone t1, t_tableone t2where t1.PARENT=t2.PARENTand t1.ORG_PARENT=t2.ORG_PARENTand t1.GRP_CD=t2.GRP_CDand t1.US_TYPE=t2.US_TYPEand t1.START_NO=t2.START_NOand t1.END_NO=t2.END_NOand t1.START_SEQ=t2.START_SEQand t1.END_SEQ=t2.END_SEQand t1.child!=t2.childand t1.LOCATION!=t2.LOCATION--查询结果4700 2151 A17A 01 C 0 0 0 0 CN024700 2152 A17A 01 C 0 0 0 0 CN024700 2153 A17A 01 C 0 0 0 0 CN03
