--1:tryselect A.ID,B.Num,A.Cost1,A.Cost2,B.Cost1,B.Cost2,B.ECode, B.CC,B.SCC,B.BCode,B.Tele from a,b,c where a.id=b.id and b.ECode=c.ecode and b.CC=c.cc and b.SCC=c.scc and a.Cost1<>b.cost1 and a.Cost2<>b.cost2;--2:don't understand
TO: ORARichard(没钱的日子......) 你写的第一个问题的SQL查出来的是空值 如果 select * from b,c where b.ECode=c.ecode and b.CC=c.cc and b.SCC=c.scc and 能查到表B中的ECode,CC,SCC在表C中存在的值 我还有一个条件,就是表A的ID要在表B中存在
--2: tryselect A.ID,B.Num,A.TType,B.ECode, B.CC,B.SCC,B.BCode,A.Cost1,A.Cost2, decode(b.id,null,' ',B.ACType) actype from ( select id,ttype,cost1,cost2,decode(sign(mdi-0),1,'B','G') mdi from a ) a, ( select id,Num,ECode,CC,SCC,BCode,decode(sign(ACType-2),1,'B','G') actype from b ) b where a.id=b.id(+) and a.mdi<>decode(b.id,null,' ',B.ACType);
B.CC,B.SCC,B.BCode,B.Tele
from a,b,c
where a.id=b.id and
b.ECode=c.ecode and b.CC=c.cc and b.SCC=c.scc and
a.Cost1<>b.cost1 and a.Cost2<>b.cost2;--2:don't understand
要求:
1.如表A的MDI>0,则MDI为'B',否则MDI为'G'(MDI为表A中一字段,这里不是update MDI,而是用decode转换MDI的值)
2.如果表A的ID在表B中存在,且表B的ACType>2,则ACType为'B',如果表A的ID在表B中存在,且表B的ACType<=2,则ACType为'G',如果表A的ID在表B中不存在,则表B的ACType为' '
3.利用满足条件1找出来的表A中的MDI和满足条件2的表B中的找出来的ACType作比较,找出表A中MDI不等于表B中ACType所有记录
4.表B中的Num和ID一一对应这样写不知道清楚不?
select出来的结果需要如下:
A.ID B.Num A.TType B.ECode B.CC B.SCC B.BCode A.Cost1 A.Cost2 B.ACType
如果
select *
from b,c
where b.ECode=c.ecode and b.CC=c.cc and b.SCC=c.scc and
能查到表B中的ECode,CC,SCC在表C中存在的值
我还有一个条件,就是表A的ID要在表B中存在
谢谢
B.CC,B.SCC,B.BCode,A.Cost1,A.Cost2,
decode(b.id,null,' ',B.ACType) actype
from
(
select id,ttype,cost1,cost2,decode(sign(mdi-0),1,'B','G') mdi from a
) a,
(
select id,Num,ECode,CC,SCC,BCode,decode(sign(ACType-2),1,'B','G') actype from b
) b
where a.id=b.id(+) and a.mdi<>decode(b.id,null,' ',B.ACType);