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
解决方案 »
- 大家有在Windows server 2012 环境下安装 oracle 11G的么?
- gc current block 2-way 冲到了等待事件 TOP1,高人过来瞄一眼,谢谢
- oracle数据同步?
- 这SQL文是干什么用的?其结果说明了什么?
- 急问怎要查出某字段连续出现3过次的记录
- 帮忙写个触发器?
- 一个奇怪的问题,从LONG RAW字段里取出照片!
- OCISessionBegin: ORA-12705: invalid or unknown NLS parameter value specified 错误是什么错误,该如何解决?
- 紧急求救!!!!!!!!!!!!
- 遇到了一个比较大的SQL,消耗了我非常多的系统资源,在线等各位大神帮忙指点
- oracle ebs reports开发如何获得for循环的次数??????
- 求SQL
--测试表创建以及测试数据插入
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_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