select Items.MatrID,sum(ItemIn.Cost) as InCost
into temp001
from Items
left join ItemOut on Items.MatrID = ItemOut.MatrID
left join ItemIn on Items.MatrID = ItemIn.MatrID
left join ItemBack on Items.MatrID = ItemBack.MatrID
where (Items.Date between :Date1 and :Date2)
group by Items.MatrID
order by Items.MatrID表Items JOIN 了三个表ItemOut,ItemIn.ItemBack,这个语句在SQL中执行完全没有问题(使用同样的表),但是在Delphi6 + ado + Access中执行不了。
请问应该怎么修改?
into temp001
from Items
left join ItemOut on Items.MatrID = ItemOut.MatrID
left join ItemIn on Items.MatrID = ItemIn.MatrID
left join ItemBack on Items.MatrID = ItemBack.MatrID
where (Items.Date between :Date1 and :Date2)
group by Items.MatrID
order by Items.MatrID表Items JOIN 了三个表ItemOut,ItemIn.ItemBack,这个语句在SQL中执行完全没有问题(使用同样的表),但是在Delphi6 + ado + Access中执行不了。
请问应该怎么修改?
在ACCESS中,是日期是前后用#号包括的。如: #2002-08-11#
into temp001
from Items
left join ItemOut on Items.MatrID = ItemOut.MatrID
left join ItemIn on Items.MatrID = ItemIn.MatrID
left join ItemBack on Items.MatrID = ItemBack.MatrID
group by Items.MatrID
order by Items.MatrID但是我只Join一个表的话在Delphi中就可以
select Items.MatrID,sum(ItemIn.Cost) as InCost
into temp001
from Items
where MatrID in
((select Matrid from ItemOut)
union (select MatrID from ItemIn)
union (select MatrID from ItemBack))
group by Items.MatrID
select Items.MatrID,sum(ItemIn.Cost) as InCost
into temp001
from ItemOut,ItemIn,ItemBack
left join ItemOut on Items.MatrID = ItemOut.MatrID
left join ItemIn on Items.MatrID = ItemIn.MatrID
left join ItemBack on Items.MatrID = ItemBack.MatrID
group by Items.MatrID
order by Items.MatrID
into temp001
from ItemOut,ItemIn,ItemBack
left join ItemOut on Items.MatrID = ItemOut.MatrID
left join ItemIn on Items.MatrID = ItemIn.MatrID
left join ItemBack on Items.MatrID = ItemBack.MatrID
group by Items.MatrID
order by Items.MatrID
select Items.MatrID,sum(ItemIn.Cost) as InCost,sum(ItemOut.Cost) as OutCost,sum(ItemBack.Cost) as BackCost
into temp001
from Items
left join ItemOut on Items.MatrID = ItemOut.MatrID
left join ItemIn on Items.MatrID = ItemIn.MatrID
left join ItemBack on Items.MatrID = ItemBack.MatrID
group by Items.MatrID
order by Items.MatrID