多个表联合查询插入解决办法

多个表联合查询插入

SQL code

INSERT INTO [wygl_pb].[dbo].[Wygl_Charge_ChargeAccountingMaster]           (           [ElectricSum]           ,[WaterSum]           ,[AdministrateSum]                      ,[paidChargettlSum]           ,NopaidChargettlSum           ,[ChargeSum]) (select sum(a.ElectricCharge) as ElectricSum,sum(b.WaterCharge) as watersum,sum(c.AdministrateSum) as AdministrateSum from Wygl_Charge_ElectricCharge as a,Wygl_Charge_WaterCharge as b,wygl_Charge_ChargeAdministrate as c) (select (sum(a.ElectricCharge)+sum(b.WaterCharge)+sum(c.AdministrateSum)) as paidChargettlSum from Wygl_Charge_ElectricCharge as a,Wygl_Charge_WaterCharge as b,wygl_Charge_ChargeAdministrate as c where a.ispaid=1 and b.ispaid=1 and c.ispaid=1) (select (sum(a.ElectricCharge)+sum(b.WaterCharge)+sum(c.AdministrateSum)) as NopaidChargettlSum from Wygl_Charge_ElectricCharge as a,Wygl_Charge_WaterCharge as b,wygl_Charge_ChargeAdministrate as c where a.ispaid=0 and b.ispaid=0 and c.ispaid=0) (select (sum(a.ElectricCharge)+sum(b.WaterCharge)+sum(c.AdministrateSum)) as ChargeSum from Wygl_Charge_ElectricCharge as a,Wygl_Charge_WaterCharge as b,wygl_Charge_ChargeAdministrate as c)

SQL code

或用CROSS JOIN     INSERT INTO [wygl_pb].[dbo].[Wygl_Charge_ChargeAccountingMaster] ( [ElectricSum] ,[WaterSum] ,[AdministrateSum] ,[paidChargettlSum] ,NopaidChargettlSum ,    [ChargeSum])         SELECT *     FROM     (select sum(a.ElectricCharge) as ElectricSum,sum(b.WaterCharge) as watersum,sum(c.AdministrateSum) as AdministrateSum     from Wygl_Charge_ElectricCharge as a,Wygl_Charge_WaterCharge as b,wygl_Charge_ChargeAdministrate as c)     AS t1    CROSS JOIN     (select (sum(a.ElectricCharge)+sum(b.WaterCharge)+sum(c.AdministrateSum)) as paidChargettlSum     from Wygl_Charge_ElectricCharge as a,Wygl_Charge_WaterCharge as b,wygl_Charge_ChargeAdministrate as c     where a.ispaid=1 and b.ispaid=1 and c.ispaid=1)      AS t2    CROSS JOIN         (select (sum(a.ElectricCharge)+sum(b.WaterCharge)+sum(c.AdministrateSum)) as NopaidChargettlSum     from Wygl_Charge_ElectricCharge as a,Wygl_Charge_WaterCharge as b,wygl_Charge_ChargeAdministrate as c where a.ispaid=0 and b.ispaid=0 and c.ispaid=0)      AS t3     CROSS JOIN     (select (sum(a.ElectricCharge)+sum(b.WaterCharge)+sum(c.AdministrateSum)) as ChargeSum from Wygl_Charge_ElectricCharge as a,Wygl_Charge_WaterCharge as b,    wygl_Charge_ChargeAdministrate as c) as      AS t4