1.
select * from A where a.a1 not in (select a1 from D)
or select a1 from A
minus
select a1 from D
2.
select * from A,C where A.a2=C.a2 and A.a3=C.a3 and A.a4=C.a4 3.
A表中a2,a3,a4 的代码在B表中有匹配的显示出来--在B表中没有a4字段4.受到3的影响.
select * from A where a.a1 not in (select a1 from D)
or select a1 from A
minus
select a1 from D
2.
select * from A,C where A.a2=C.a2 and A.a3=C.a3 and A.a4=C.a4 3.
A表中a2,a3,a4 的代码在B表中有匹配的显示出来--在B表中没有a4字段4.受到3的影响.
select * from A,B where A.a2 = C.a2 and A.a3 = C.a4 and A.a5 = C.a5 and not exists (select * from D where A.a1 = D.a1)
不明白你的:
3.A表中a2,a3,a4 的代码在B表中有匹配的显示出来
说明b表中a1字段的内容1表示A表中a1,2表示a2,3表示a3 这样把他代码对应的B表中a3内容显示出来。
where B.a1=A.a2 --匹配所有AB表
and B.a2=decode(B.a1,'1',A.a2,'2',A.a3,'3',A.a4,'') --满足说明和条件3
and A.a1 not in (select a1 from D) --满足条件1
and (A.a2 in (select distinct a2 from C) or
A.a3 in (select distinct a3 from C) or
A.a4 in (select distinct a4 from C)); --满足条件2
---------------------------------
select B.* from B,A
where B.a1=A.a2 --匹配所有AB表
and B.a2=decode(B.a1,'1',A.a2,'2',A.a3,'3',A.a4,'') --满足说明条件和条件3
and A.a1 not in (select a1 from D) --满足条件1
and A.a2 in (select distinct a2 from C)
and A.a3 in (select distinct a3 from C)
and A.a4 in (select distinct a4 from C)); --满足条件2
说明b表中a1字段的内容1表示A表中a1,2表示a2,3表示a3 这样把他代码对应的B表中a3内容显示出来”此话是否应该为“a1字段的内容1表示A表中a2,2表示a3,3表示a4 ”。select a1, (
select B.a3 from B where B.a1=1 and B.a2 = A.a2
) as ca2,
(
select B.a3 from B where B.a1=2 and B.a2 = A.a3
) as ca3,(
select B.a3 from B where B.a1=3and B.a2 = A.a4
) as ca4
from A where a.a1 not in (select a1 from D) and exists (
select * from C where A.a2 = C.a2 and A.a3 = C.a4 and A.a5 = C.a5
)
having not ca2 is null and not ca3 is null and not ca4 is null
;orselect a1, ca2 , ca3, ca4
from A (
select B.a2, B.a3 as ca2 from B where B.a1=1
) B1, (
select B.a2, B.a3 as ca3 from B where B.a1=2
) B2,(
select B.a2, B.a3 as ca4 from B where B.a1=3
) B3
where A.a1 not in (select a1 from D) and exists (
select * from C where A.a2 = C.a2 and A.a3 = C.a4 and A.a5 = C.a5
) and B1.a2 = A.a2 and B2.a2 = A.a3 and B3.a2 = A.a4
dinya2003(OK) ( )
是我写错了。是b表中a1的字段:1代表a表中a2,2代表a3,3代表a4各位我的意思是一条SQL满足这三个条件啊。同时满足这三个条件的所有记录
2.A表中a2,a3,a4在C表中有对应的记录显示出来,必须是a表中a2,a3,a4在c表中a2,a3,a4 存在这条记录a2,a3,a4是并的关系,如果存在就显示a表中这条记录
3.A表中a2,a3,a4 的代码在B表中有匹配的显示出来
说明b表中a1字段的内容1表示A表中a2,2表示a3,3表示a4 这样把他代码对应的B表中a3内容显示出来。
最后就是必须一次满足这3个条件的记录找出来。这条SQL语句该怎么写,出来的记录必须满足这三个条件。
2:符合条件二的A表记录是没有的,即A表的a2,a3,a4同时出现在C表的a2,a3,a4
from A (
select B.a2, B.a3 as ca2 from B where B.a1=1
) B1, (
select B.a2, B.a3 as ca3 from B where B.a1=2
) B2,(
select B.a2, B.a3 as ca4 from B where B.a1=3
) B3, C
where A.a1 not in (select a1 from D)
and A.a2 = C.a2 and A.a3 = C.a4 and A.a5 = C.a5
and B1.a2 = A.a2 and B2.a2 = A.a3 and B3.a2 = A.a4
肯定是有的。我只是举了例子而已。。我不可把记录全部列出来嘛。。呵呵。
为什么这句提示没有确定列呢?急