select count(1)
from t_im_moveissuebill a
left join t_im_moveissuebillentry b
on a.fid = b.fparentid
left join t_bot_relation r
on a.fid = r.fsrcobjectid
left join t_im_moveinwarehsbillentry ib
on r.fdestobjectid = ib.fparentid
and b.fmaterialid = ib.fmaterialid
and b.fassistpropertyid = ib.fassistpropertyid
where to_char(A.Fbizdate, 'yyyy-mm-dd') >= '2013-08-01' and
to_char(A.Fbizdate, 'yyyy-mm-dd') <= '2013-08-15' and exists
(select 1 from TT_WHEREVALUES W where W.FID = B.FWAREHOUSEID)
and NVL(A.FAUDITORID, ' ') <> ' '
and NVL(A.FIsInitBill, 0) = 0
以上的写法我获得的行数结果是9457.
下边的写法获得的行数结果是9331.
select count(1)
from t_im_moveissuebill a,
t_im_moveissuebillentry b,
t_bot_relation r,
t_im_moveinwarehsbillentry ib
where a.fid = b.fparentid
and to_char(A.Fbizdate, 'yyyy-mm-dd') >= '2013-08-01'
and to_char(A.Fbizdate, 'yyyy-mm-dd') <= '2013-08-15'
and exists
(select 1 from TT_WHEREVALUES W where W.FID = B.FWAREHOUSEID)
and NVL(A.FAUDITORID, ' ') <> ' '
and NVL(A.FIsInitBill, 0) = 0
and a.fid = r.fsrcobjectid(+)
and r.fdestobjectid = ib.fparentid(+)
and b.fmaterialid = ib.fmaterialid
and b.fassistpropertyid = ib.fassistpropertyid那么像上一段的多条件的left join在简写时,应该如何在where后边编写呢??
from t_im_moveissuebill a
left join t_im_moveissuebillentry b
on a.fid = b.fparentid
left join t_bot_relation r
on a.fid = r.fsrcobjectid
left join t_im_moveinwarehsbillentry ib
on r.fdestobjectid = ib.fparentid
and b.fmaterialid = ib.fmaterialid
and b.fassistpropertyid = ib.fassistpropertyid
where to_char(A.Fbizdate, 'yyyy-mm-dd') >= '2013-08-01' and
to_char(A.Fbizdate, 'yyyy-mm-dd') <= '2013-08-15' and exists
(select 1 from TT_WHEREVALUES W where W.FID = B.FWAREHOUSEID)
and NVL(A.FAUDITORID, ' ') <> ' '
and NVL(A.FIsInitBill, 0) = 0
以上的写法我获得的行数结果是9457.
下边的写法获得的行数结果是9331.
select count(1)
from t_im_moveissuebill a,
t_im_moveissuebillentry b,
t_bot_relation r,
t_im_moveinwarehsbillentry ib
where a.fid = b.fparentid
and to_char(A.Fbizdate, 'yyyy-mm-dd') >= '2013-08-01'
and to_char(A.Fbizdate, 'yyyy-mm-dd') <= '2013-08-15'
and exists
(select 1 from TT_WHEREVALUES W where W.FID = B.FWAREHOUSEID)
and NVL(A.FAUDITORID, ' ') <> ' '
and NVL(A.FIsInitBill, 0) = 0
and a.fid = r.fsrcobjectid(+)
and r.fdestobjectid = ib.fparentid(+)
and b.fmaterialid = ib.fmaterialid
and b.fassistpropertyid = ib.fassistpropertyid那么像上一段的多条件的left join在简写时,应该如何在where后边编写呢??
t_im_moveissuebill 加上你的限定条件是你查出的条数
后面的那个 a.fid = b.fparentid你做了关联 等于加了限定条件 所以会少些
select count(1)
from t_im_moveissuebill a,
t_im_moveissuebillentry b,
t_bot_relation r,
t_im_moveinwarehsbillentry ib
where a.fid = b.fparentid(+)
and to_char(A.Fbizdate, 'yyyy-mm-dd') >= '2013-08-01'
and to_char(A.Fbizdate, 'yyyy-mm-dd') <= '2013-08-15'
and exists
(select 1 from TT_WHEREVALUES W where W.FID = B.FWAREHOUSEID)
and NVL(A.FAUDITORID, ' ') <> ' '
and NVL(A.FIsInitBill, 0) = 0
and a.fid = r.fsrcobjectid(+)
and r.fdestobjectid = ib.fparentid(+)
and b.fmaterialid = ib.fmaterialid(+)
and b.fassistpropertyid = ib.fassistpropertyid(+)