请教SQL语句 - 专家坐堂 - Foxtable(狐表) - 新一代数据库软件，完美融合Access、Foxpro、Excel、vb.net之优势，人人都能掌握的快速软件开发工具!

大师：请问下面的SQL语句，如果数据源是SQL，没问题。如果改为access数据源，为何不对？

S——————E_LECT t_Item.FNumber as xmid,t_Item.FName as xmname, Sum(t_Balance.FBeginBalance) AS je,t_Account.FNumber as kmid,t_Account.FName as kmname FROM (t_Item INNER JOIN t_ItemDetailV ON t_Item.FItemID=t_ItemDetailV.FItemID) INNER JOIN t_Balance ON t_ItemDetailV.FDetailID=t_Balance.FDetailID left join t_Account on t_Account.FAccountID = t_Balance.FAccountID WHERE (t_Balance.FYear=2014 and t_Balance.FPeriod=1 And t_Balance.FCurrencyID=0 And t_Balance.FDetailID>0) GROUP BY t_Balance.FAccountID,t_Item.FItemClassID,t_Item.FItemID,t_Item.FNumber,t_Item.FName,t_Account.FNumber,t_Account.FName

access按这种模式使用多表连接，一层套一层

select * from (({表A} inner join {表B} on {表A}.第一列={表B}.第一列) inner join {表C} on {表A}.第一列={表C}.第一列) inner join {表D} on {表A}.第一列={表D}.第一列

谢谢！下面的语句在access数据源中执行出现“join不支持表达式”，但在sql数据源中可以。

Dim pztbzfc As String = ""

pztbzfc = "S————E_LECT pzFL.FVoucherID,pzM.FDate as [date],pzM.FPeriod as [month],pzM.FGroupID,pzM.FNumber,pzM.FPosted as 记账标志,pzFL.FEntryID,pzFL.FExplanation as zy,pzFL.FAccountID as kmid,jej,jed," & _

" pzFL.FCurrencyID,pzFL.FExchangeRate,pzFL.FDC,pzFL.FAmountFor,pzFL.FAmount,pzFL.FQuantity,pzFL.FMeasureUnitID,pzFL.FUnitPrice,pzFL.FDetailID,pzfz.FName + '-' + Format(pzm.FNumber,'') as pzlbname,km.FNumber as kmid,km.FName as kmname,Item8.FName as xm,Item8.FNumber as xmdl,Item9.FName as xm2,Item9.FNumber as xmdl2,Item10.FName as xm3,Item10.FNumber as xmdl3,Item11.FName as xm4" & _

" FROM ((((((((t_Voucher As pzM INNER JOIN t_VoucherEntry As pzFL ON pzM.FVoucherID = pzFL.FVoucherID) left join t_VoucherGroup pzfz on pzM.FGroupID = pzfz.FGroupID) left join t_Account km on km.FAccountID = pzfl.FAccountID)" & _

" left JOIN t_ItemDetailV tailV ON pzfl.FDetailID = tailv.FDetailID)" & _

" left JOIN t_Item item8 ON item8.FItemID = tailv.fitemid And (tailv.fitemclassid = 1 Or tailv.fitemclassid = 8))" & _

" left JOIN t_Item item9 ON item9.FItemID = tailv.fitemid And (tailv.fitemclassid = 2))" & _

" left JOIN t_Item item10 ON item10.FItemID = tailv.fitemid And (tailv.fitemclassid = 3))" & _

" left JOIN t_Item item11 ON item11.FItemID = tailv.fitemid And (tailv.fitemclassid > 3 And tailv.fitemclassid <> 8))" & _

" where pzM.FYear = #[年]# And pzM.FPeriod<= 12 ORDER BY pzM.FPeriod,pzm.FNumber,pzM.FVoucherID,pzFL.FEntryID"

难道在access数据源中，on条件不能用and 组合条件？那么这些条件写在哪里？

那就没办法，只能用sql server吧

那就使用子查询，根据条件查询后再连接。或者直接按条件在access里创建一个查询表进行连接

那就使用子查询，根据条件查询后再连接，语句怎么写？举个例子？

select * from (({表A} inner join {表B} on {表A}.第一列={表B}.第一列) inner join (select * from {表B} where 第一列=‘123’) as c on {表A}.第一列=c.第一列) inner join {表D} on {表A}.第一列={表D}.第一列

谢谢！那上述的语句，在sql数据源中是否也可以？

另外，SQL语句中的 where条件是哪个表的筛选条件？，还是整个语句的筛选？是不是笛卡尔乘积后筛选条件？如果是整个条件都可以写在这里？但效率太差？

比如建立视图或者查询：视图1

select FName,FNumber,FItemID from t_ItemDetailV tailV innner join t_Item a on item8.FItemID = tailv.fitemid where tailv.fitemclassid = 1 Or tailv.fitemclassid = 8

left JOIN t_Item item8 ON item8.FItemID = tailv.fitemid And (tailv.fitemclassid = 1 Or tailv.fitemclassid = 8))

可以改为

left JOIN 视图1 item8 ON item8.FItemID = tailv.fitemid )

也就是说，access数据源的sql查询语句外连接时，on条件中只能有一个等值条件。

如果是sql数据源可以有多个条件？

主题：请教SQL语句