日期:2014-05-17 浏览次数:20894 次
PARENT CHILD ORG_PARENT GRP_CD US_TYPE START_NO END_NO START_SEQ END_SEQ LOCATION 4700 2151 A17A 01 C 0 0 0 0 CN02 4700 2151 A17A 01 C 1 1 1 1 CN04 4700 2152 A17A 01 C 0 0 0 0 CN02 4700 2152 A17A 01 C 1 1 1 1 CN04 4700 2153 A17A 01 C 0 0 0 0 CN03 4700 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 LOCATION 4700 2151 A17A 01 C 0 0 0 0 CN02 4700 2152 A17A 01 C 0 0 0 0 CN02 4700 2153 A17A 01 C 0 0 0 0 CN03 下面一组数据因为除了child不相同以外,其他字段的值都相等,所以不会被查询出来。 4700 2151 A17A 01 C 1 1 1 1 CN04 4700 2152 A17A 01 C 1 1 1 1 CN04 4700 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 all select '4700','2151','A17A','01','C','1','1','1','1','CN04' from dual union all select '4700','2152','A17A','01','C','0','0','0','0','CN02' from dual union all select '4700','2152','A17A','01','C','1','1','1','1','CN04' from dual union all select '4700','2153','A17A','01','C','0','0','0','0','CN03' from dual union all select '4700','2154','A17A','01','C','1','1','1','1','CN04' from dual --查询语句 select distinct t2.* from t_tableone t1, t_tableone t2 where t1.PARENT=t2.PARENT and t1.ORG_PARENT=t2.ORG_PARENT and t1.GRP_CD=t2.GRP_CD and t1.US_TYPE=t2.US_TYPE and t1.START_NO=t2.START_NO and t1.END_NO=t2.END_NO and t1.START_SEQ=t2.START_SEQ and t1.END_SEQ=t2.END_SEQ and t1.child!=t2.child and t1.LOCATION!=t2.LOCATION --查询结果 4700 2151 A17A 01 C 0 0 0 0 CN02 4700 2152 A17A 01 C 0 0 0 0 CN02 4700 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 all select '4700','2151','A17A','01','C','1','1','1','1','CN04' from dual union all select '4700','2152','A17A','01','C','0','0','0','0','CN02' from dual union all select '4700','2152','A17A','01','C','1','1','1','1','CN04' from dual union all select '4700','2153','A17A','01','C','0','0','0','0','CN03' from dual union all select '4700','2154','A17A','01','C','1','1','1','1','CN04' from dual --查询语句 select distinct t2.* from t_tableone t1, t_tableo