利用union 將多個select 的結果結合起來.union命令將來自不同select命令所生成的數據混合在一起,並將重復的數據刪除僅留下一條,也就是數學上的並集關系。
如果你想讓重復的數據也留下時,可以使用union all取代原來的union命令
1.select prod_id ,sup_id
from sale_item
where order_no=10001
union
select prod_id,sup_id
from purchase
where pur_no=50001
order by 1,2
2.在sqlserver方面可以用case實現的,但在Access裡,只能用iif或union
select a.rmpwr,a.buypwr,yd-case when b.dl>0 then a.yd+b.rmpwr-b.bdl
##www.ASPxuexi.com##
else a.yd-b.yd end
from (select mid,rmpwr,buypwr,(buypwr-rmpwr)as yd from main where month(dt)=2)a,(select mid,rmpwr,buypwr,(buypwr-rmpwr)as yd,bdl from main where month(dt)=1 b where a.mid=b.mid
在本機數據庫中a,b同上
select a.ydl+b.rmpwr-b.ydl as yd from a,b where a.mid=b.mid and b.bdl>0
union all
select a.yd-b.yd from a,b where a.mid=b.mid and isnull(b.bdl,-1)<=0