第一个记录很简单:
select A.i,A.a3,B,b2
from A,B
where A.i=B.i and A.a1="xxx" and B.b2="xx"第二个你没说清楚,什么叫“表A中有,而表B中没有”
select A.i,A.a3,B,b2
from A,B
where A.i=B.i and A.a1="xxx" and B.b2="xx"第二个你没说清楚,什么叫“表A中有,而表B中没有”
调试欢乐多
下面是第二种查询:
select i
from A
where i not in (select a.i from A,B where A.i=B.i and A.al='xxx' and B.b2='xx') and i not in (selelct i from B)
条件:我觉得可以这样描述,
select A.i
form A
where A.i not in (select B.i form 第一种纪录组成的集合)但是一来我不直到SQL语句怎么写,请大家指教
另外一方面我们头要求我最好用一个SQL得到两种纪录也就是得到这样的结果表a:
-------------------------
| i | a1 | a2 | a3 |
-------------------------
|M012| xxx | uuu | abc |
|M013| ddd | www | uuu |
|M014| xxx | sss | bbb |
|M015| ppp | yyy | uuu |
-------------------------表b:
-------------------------
| i | b1 | b2 | b3 |
-------------------------
|M012| xxx | xx | yyy |
|M013| ddd | hhh | ddd |
|M014| xxx | xx | bbb |
|M015| ppp | www | rrr |
-------------------------
期望结果:
------------------
| i | a3 | b2 |
------------------
|M012| abc | uuu |
|M013| --- | --- |
|M014| bbb | sss |
|M015| --- | --- |
------------------
-------------------------
| i | b1 | b2 | b3 |
-------------------------
|M012| xxx | xx | yyy |
|M012| ddd | hhh | ddd |
|M012| fff | ttt | hhh |
|M012| ddd | ppp | ddd |
|M014| xxx | xx | sss |
|M014| ppp | www | rrr |
|M014| ppp | www | rrr |
|M014| ppp | www | rrr |
|M014| xxx | xx | rrr |
-------------------------这时候结果是------------------
| i | a3 | b3 |
------------------
|M012| abc | uuu |
|M013| --- | --- |
|M014| bbb | sss |
|M014| bbb | sss |
|M015| --- | --- |
------------------
from A,B
where A.i=B.i and A.a1="xxx" and B.b2="xx"
union
select A.i,A.a3,B,b2
from A,B
where i not in (select a.i from A,B where A.i=B.i and A.al='xxx' and B.b2='xx') and i not in (selelct i from B)