select distinct FS_Customer.CustomerID from (((FS_Customer inner join FS_COLine on FS_COLine.PromisedShipDate='2003-7-20' and FS_COLine.COLineStatus=4) ----这里等于选择符合条件的FS_Customer记录和 FS_COLine做了cross join inner join FS_COHeader on FS_COLine.COHeaderKey=FS_COHeader.COHeaderKey and FS_Customer.CustomerKey=FS_COHeader.CustomerKey) inner join FS_ShipToDeliveryLocation on FS_ShipToDeliveryLocation.DistributionRegion='HLJ' and FS_ShipToDeliveryLocation.CustomerKey=FS_Customer.CustomerKey) where FS_COLine.COLineKey not in (select COLineKey from CO_Info where PromisedShipDate='2003-7-20') order by FS_Customer.CustomerID
select distinct FS_Customer.CustomerID from ((( FS_Customer inner join (select * from FS_COLine where COLineKey not in (select COLineKey from CO_Info where PromisedShipDate='2003-7-20') and FS_COLine.PromisedShipDate='2003-7-20' and FS_COLine.COLineStatus=4) a on 条件inner join FS_COHeader on FS_COLine.COHeaderKey=FS_COHeader.COHeaderKey and FS_Customer.CustomerKey=FS_COHeader.CustomerKey) inner join FS_ShipToDeliveryLocation on FS_ShipToDeliveryLocation.CustomerKey=FS_Customer.CustomerKey and FS_ShipToDeliveryLocation.DistributionRegion='HLJ' ) order by FS_Customer.CustomerID
TO:happydreamer(Very Very Shy)那您看应该怎样改呢,我比较笨,一时还理解不了您的意思,望指明!!!谢谢!!!!!
FS_Customer inner join FS_COLine on 要加上条件 通过某个字段相连 先在FS_COLine表中选出符合条件的记录再和其他表关联,减少数据量
TO: happydreamer(Very Very Shy) 您这种方法我用了,但速度好像一样啊,是不是因为not in(select COLineKey....)集合中数据太多了,所以就慢呢???你看看我改的对不对啊,谢谢了!!!!!!!!!!!!!select distinct FS_Customer.CustomerID from ((( FS_Customer inner join (select * from FS_COLine where COLineKey not in (select COLineKey from CO_Info where PromisedShipDate='2003-7-20')) FS_COLine on
FS_COLine.PromisedShipDate='2003-7-20' and FS_COLine.COLineStatus=4)inner join FS_COHeader on FS_COLine.COHeaderKey=FS_COHeader.COHeaderKey and FS_Customer.CustomerKey=FS_COHeader.CustomerKey) inner join FS_ShipToDeliveryLocation on FS_ShipToDeliveryLocation.CustomerKey=FS_Customer.CustomerKey and FS_ShipToDeliveryLocation.DistributionRegion='HLJ' ) order by FS_Customer.CustomerID
from (((FS_Customer
inner join FS_COLine on
FS_COLine.PromisedShipDate='2003-7-20' and FS_COLine.COLineStatus=4)
----这里等于选择符合条件的FS_Customer记录和 FS_COLine做了cross join inner join FS_COHeader on
FS_COLine.COHeaderKey=FS_COHeader.COHeaderKey
and FS_Customer.CustomerKey=FS_COHeader.CustomerKey)
inner join FS_ShipToDeliveryLocation on
FS_ShipToDeliveryLocation.DistributionRegion='HLJ'
and FS_ShipToDeliveryLocation.CustomerKey=FS_Customer.CustomerKey)
where FS_COLine.COLineKey not in
(select COLineKey from CO_Info where PromisedShipDate='2003-7-20')
order by FS_Customer.CustomerID
from (((
FS_Customer inner join (select * from FS_COLine where COLineKey not in (select COLineKey from CO_Info where PromisedShipDate='2003-7-20')
and FS_COLine.PromisedShipDate='2003-7-20' and FS_COLine.COLineStatus=4) a
on 条件inner join FS_COHeader on FS_COLine.COHeaderKey=FS_COHeader.COHeaderKey and FS_Customer.CustomerKey=FS_COHeader.CustomerKey)
inner join FS_ShipToDeliveryLocation on FS_ShipToDeliveryLocation.CustomerKey=FS_Customer.CustomerKey
and FS_ShipToDeliveryLocation.DistributionRegion='HLJ' )
order by FS_Customer.CustomerID
要加上条件 通过某个字段相连
先在FS_COLine表中选出符合条件的记录再和其他表关联,减少数据量
from (((
FS_Customer inner join (select * from FS_COLine where COLineKey not in
(select COLineKey from CO_Info where PromisedShipDate='2003-7-20')) FS_COLine on
FS_COLine.PromisedShipDate='2003-7-20' and FS_COLine.COLineStatus=4)inner join FS_COHeader on FS_COLine.COHeaderKey=FS_COHeader.COHeaderKey and FS_Customer.CustomerKey=FS_COHeader.CustomerKey)
inner join FS_ShipToDeliveryLocation on FS_ShipToDeliveryLocation.CustomerKey=FS_Customer.CustomerKey
and FS_ShipToDeliveryLocation.DistributionRegion='HLJ' )
order by FS_Customer.CustomerID