strListSql = "SELECT Money.ID AS Money_ID,Money.CustomNO AS Custom_NO,Custom.M_Name as Custom_M_Name,"
strListSql = strListSql & " Custom.F_Name AS Custom_F_Name, Custom.Series AS Custom_Series,"
strListSql = strListSql & " Custom.SeriesPrice AS Custom_SeriesPrice,"
strListSql = strListSql & " Money.Date AS Money_Date, Money.Shroff AS Money_Shroff,"
strListSql = strListSql & " Money.Type As Money_Type, Money.Money AS Money_Money, Money.Re AS Money_Re "
strListSql = strListSql & " FROM Custom LEFT JOIN Money ON Custom.CustomNO=Money.CustomNO "这本身是Custom表和Money表的联合查寻,现在又增加一个Custom_Other表,这个表也和Money 表关联,请问SQL怎么写?
strListSql = strListSql & " Custom.F_Name AS Custom_F_Name, Custom.Series AS Custom_Series,"
strListSql = strListSql & " Custom.SeriesPrice AS Custom_SeriesPrice,"
strListSql = strListSql & " Money.Date AS Money_Date, Money.Shroff AS Money_Shroff,"
strListSql = strListSql & " Money.Type As Money_Type, Money.Money AS Money_Money, Money.Re AS Money_Re "
strListSql = strListSql & " FROM Custom LEFT JOIN Money ON Custom.CustomNO=Money.CustomNO "这本身是Custom表和Money表的联合查寻,现在又增加一个Custom_Other表,这个表也和Money 表关联,请问SQL怎么写?
strListSql = strListSql & " Custom.F_Name AS Custom_F_Name, Custom.Series AS Custom_Series,"
strListSql = strListSql & " Custom.SeriesPrice AS Custom_SeriesPrice,"
strListSql = strListSql & " Money.Date AS Money_Date, Money.Shroff AS Money_Shroff,"
strListSql = strListSql & " Money.Type As Money_Type, Money.Money AS Money_Money, Money.Re AS Money_Re "
strListSql = strListSql & " FROM Custom LEFT JOIN Money ON Custom.CustomNO=Money.CustomNO
left join Custom_Other on Custom_Other...=Money... "
A LEFT OUTER JOIN (SELECT * FROM B LEFT JOIN C ON B.AA=C.AA) AS D
ON(D.AA=A.AA)关系是这样的,具体的表 与条件 你套用一下。
Money.ID AS Money_ID,
Money.CustomNO AS Custom_NO,
Custom.M_Name as Custom_M_Name,
Custom.F_Name AS Custom_F_Name,
Custom.Series AS Custom_Series,
Custom.SeriesPrice AS Custom_SeriesPrice,
Money.Date AS Money_Date,
Money.Shroff AS Money_Shroff,
Money.Type As Money_Type,
Money.Money AS Money_Money,
Money.Re AS Money_Re
FROM (select * from Custom union all select * from Custom_Other) as Custom
LEFT JOIN Money
ON Custom.CustomNO=Money.CustomNO
Custom.F_Name AS Custom_F_Name, Custom.Series AS Custom_Series,
Custom.SeriesPrice AS Custom_SeriesPrice,
Money.Date AS Money_Date, Money.Shroff AS Money_Shroff,
Money.Type As Money_Type, Money.Money AS Money_Money, Money.Re AS Money_Re
FROM Custom LEFT JOIN Money ON Custom.CustomNO=Money.CustomNO
LEFT JOIN Custom_Other on Money.CustomNO = Custom_Other.CustomNO或者先连接Money,Custom_Other,然后再用子查询select 你需要的字段 from Custom left join
(select 你需要的字段 from Money m , Custom_Other n where m.CustomNO = n.CustomNO) t2
on t1.CustomNO = t2.CustomNO
strListSql = "SELECT Money.ID AS Money_ID,Money.CustomNO AS Custom_NO,Custom.M_Name as Custom_M_Name,"
strListSql = strListSql & " Custom.F_Name AS Custom_F_Name, Custom.Series AS Custom_Series,"
strListSql = strListSql & " Custom.SeriesPrice AS Custom_SeriesPrice,"
strListSql = strListSql & " Money.Date AS Money_Date, Money.Shroff AS Money_Shroff,"
strListSql = strListSql & " Money.Type As Money_Type, Money.Money AS Money_Money, Money.Re AS Money_Re "
strListSql = strListSql & " FROM Custom LEFT JOIN Money ON Custom.CustomNO=Money.CustomNO "
--Add----
strListSql = strListSql & " LEFT JOIN Custom_Other ON Custom_Other.CustomNO = Money.CustomNO "
2.如果Money表需要关联Custom和Custom_Other的合集且两表数据结构一致,则: strListSql = "SELECT Money.ID AS Money_ID,Money.CustomNO AS Custom_NO,NewCustom .M_Name as Custom_M_Name,"
strListSql = strListSql & " NewCustom .F_Name AS Custom_F_Name, NewCustom .Series AS Custom_Series,"
strListSql = strListSql & " NewCustom .SeriesPrice AS Custom_SeriesPrice,"
strListSql = strListSql & " Money.Date AS Money_Date, Money.Shroff AS Money_Shroff,"
strListSql = strListSql & " Money.Type As Money_Type, Money.Money AS Money_Money, Money.Re AS Money_Re "
--change------------
strListSql = strListSql & " FROM (Select * From Custom Union Select * From Custom_Other) NewCustom LEFT JOIN Money ON NewCustom .CustomNO=Money.CustomNO "