Baan ERP SQL Query 中Exists的用法举例

很久都没有写Baan的报表以及查询了,上午远在深圳的Allen通过MSN告诉我一个小小的需求,于是用到了Exists的查询,代码分享如下:

正常的关联写法

select
tdsls401.orno,
tdsls401.pono,
tdsls401.sqnb
from
tdsls401, | Sales Order Lines
whinh430, | Shipment
whinh431 | Shipment Lines
where
whinh431.worn = tdsls401.orno and
whinh431.wpon = tdsls401.pono and
whinh431.wseq = tdsls401.sqnb and
whinh430.pcsp = tcyesno.yes and
whinh431.shpm = whinh430.shpm and
(whinh431.worg = whinh.oorg.sales or
whinh431.worg = whinh.oorg.purchase)
order by tdsls401.orno,tdsls401.pono

反选的时候,用Not Exists

select
tdsls401.orno,
tdsls401.pono,
tdsls401.sqnb
from
tdsls401 | Sales Order Lines
where not exists
(select *
from
whinh430, | Shipment
whinh431 | Shipment Lines
where
whinh431.worn = tdsls401.orno and
whinh431.wpon = tdsls401.pono and
whinh431.wseq = tdsls401.sqnb and
whinh430.pcsp = tcyesno.yes and
whinh431.shpm = whinh430.shpm and
(whinh431.worg = whinh.oorg.sales or
whinh431.worg = whinh.oorg.purchase))
order by tdsls401.orno,tdsls401.pono

其实第一种正常查询中,查询多个表中关联出现的结果集也可以用Exists:

select
tdsls401.orno,
tdsls401.pono,
tdsls401.sqnb
from
tdsls401 | Sales Order Lines
where exists
(select *
from
whinh430, | Shipment
whinh431 | Shipment Lines
where
whinh431.worn = tdsls401.orno and
whinh431.wpon = tdsls401.pono and
whinh431.wseq = tdsls401.sqnb and
whinh430.pcsp = tcyesno.yes and
whinh431.shpm = whinh430.shpm and
(whinh431.worg = whinh.oorg.sales or
whinh431.worg = whinh.oorg.purchase))
order by tdsls401.orno,tdsls401.pono

发表评论

电子邮件地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据