日期:2014-05-17  浏览次数:20808 次

因为之前的描述不清楚,重新发帖求助SQL。。。
SQL code

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




------解决方案--------------------
SQL code

--测试表创建以及测试数据插入
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

------解决方案--------------------
SQL code

--测试表创建以及测试数据插入
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