等一個晴天

寫程式是種美學，為了追求想要一個query string把所有的工作給做掉
我困擾了很多天怎麼計算兩張table的差異

1.先select table中所有的元素然後跟vender是notel做差集
2.再用那個做出來的差集去跟tellab做差集
如此一來就可以算出pams這張table中有多少東西是pathtable所沒有的

select name from pams
minus
select  pams.name
from pams, pathtable550
where  pams.name  in (select userlabel from  pathtable550 where vendorid= 'NTL' and pathtable550.rate like 'VC%')
and  pathtable550.userlabel= pams.NAME
minus
select distinct pams.name
from pams, pathtable550
where  pams.name  in (select substr(userlabel, 1, instr(userlabel,'.')-1) from pathtable550 where vendorid ='TEL')


很感謝鵬鵬老師多年前出的那本書我沒丟
一直當它是oracle的字典跟聖經來用..XD


完整版如下：
---pams有的但pathtable沒有的資料---
select name from pams
minus
select  pams.name
from pams, pathtable550
where  pams.name  in (select userlabel from  pathtable550 where vendorid= 'NTL' and pathtable550.rate like 'VC%')
and  pathtable550.userlabel= pams.NAME
minus
select distinct pams.name
from pams, pathtable550
where  pams.name  in (select substr(userlabel, 1, instr(userlabel,'.')-1) from pathtable550 where vendorid ='TEL')

---pathtable有pams沒有的---
---Notel
select userlabel from pathtable550
where vendorid= 'NTL' and pathtable550.rate like 'VC%'
minus
select  pams.name
from pams, pathtable550
where  pams.name  in (select userlabel from  pathtable550 where vendorid= 'NTL' and pathtable550.rate like 'VC%')
and  pathtable550.userlabel= pams.NAME
---TELLAB
select substr(userlabel, 1, instr(userlabel,'.')-1)  from pathtable550
where vendorid= 'TEL'
minus
select distinct pams.name
from pams, pathtable550
where  pams.name  in (select substr(userlabel, 1, instr(userlabel,'.')-1) from pathtable550 where vendorid ='TEL')