日期:2014-05-18  浏览次数:20755 次

多个表联合查询插入
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