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

sql语句的写法
select  
ICsale.finterid,t_Organization.Fname   as   [客户名称   (Client)],ICsale.Fbillno   as   [发票号码       (Inv.   No)],   ICsale.Fdate   as   [发票日期     (Inv.   Date)]
,Sum(ICSaleEntry.FAmount)   AS   [     货物净值         (Net   value)],   Sum(ICSaleEntry.FTaxAmount)   AS   [税   额   (VAT)],ICsale.FHeadselfi0453   as   [       运         费             (Transport   cost)],sum(ICsaleentry.FAmountincludetax)   as   [   价税合计         (Total   Amount)]
,ICsale.FHeadselfi0464   as   [应回款日   (Due   date)]
,t_RP_NewCheckInfo.FcheckamountFor     as   [实际回款金额       (Amount   cashed)]   ,t_RP_NewCheckInfo.fcheckdate   as   [实际回款日期   (Date   of   cash)]

from   ICsale   JOIN   ICsaleentry   on     ICsale.finterid   =ICsaleentry.finterid  
                        JOIN   t_Organization   on     ICsale.FcustID   =t_Organization.FitemID    
left   join   t_RP_NewCheckInfo   on   ICsale.finterid=t_RP_NewCheckInfo.Fbillid   and   t_RP_NewCheckInfo.ftype=3
           
      WHERE    
        ISNULL(t_RP_NewCheckInfo.FcheckamountFor,0)=0          
GROUP   BY   ICSale.FInterID,ICsale.Fbillno,ICsale.Fdate,ICsale.FHeadselfi0453,ICsale.FHeadselfi0464,t_Organization.Fname
,t_RP_NewCheckInfo.FcheckamountFor,t_RP_NewCheckInfo.fcheckdate


想对以上语句的

Sum(ICSaleEntry.FAmount)   AS   [     货物净值         (Net   value)],   Sum(ICSaleEntry.FTaxAmount)   AS   [税   额   (VAT)],
ICsale.FHeadselfi0453   as   [       运         费             (Transport   cost)],sum(ICsaleentry.FAmountincludetax)   as   [   价税合计         (Total   Amount)]


继续求和.条件是(Client)],ICsale.Fbillno   as   [发票号码       (Inv.   No)],不相同


------解决方案--------------------
select [客户名称 (Client)],[发票号码 (Inv. No)],sum([ 货物净值 (Net value)]),sum([税 额 (VAT)]),sum([ 运 费 (Transport cost)]),sum([ 价税合计 (Total Amount)])
from(
select
ICsale.finterid,t_Organization.Fname as [客户名称 (Client)],ICsale.Fbillno as [发票号码 (Inv. No)], ICsale.Fdate as [发票日期 (Inv. Date)]
,Sum(ICSaleEntry.FAmount) AS [ 货物净值 (Net value)], Sum(ICSaleEntry.FTaxAmount) AS [税 额 (VAT)],ICsale.FHeadselfi0453 as [ 运 费 (Transport cost)],sum(ICsaleentry.FAmountincludetax) as [ 价税合计 (Total Amount)]
,ICsale.FHeadselfi0464 as [应回款日 (Due date)]
,t_RP_NewCheckInfo.FcheckamountFor as [实际回款金额 (Amount cashed)] ,t_RP_NewCheckInfo.fcheckdate as [实际回款日期 (Date of cash)]

from ICsale JOIN ICsaleentry on ICsale.finterid =ICsaleentry.finterid
JOIN t_Organization on ICsale.FcustID =t_Organization.FitemID
left join t_RP_NewCheckInfo on ICsale.finterid=t_RP_NewCheckInfo.Fbillid and t_RP_NewCheckInfo.ftype=3

WHERE
ISNULL(t_RP_NewCheckInfo.FcheckamountFor,0)=0
GROUP BY ICSale.FInterID,ICsale.Fbillno,ICsale.Fdate,ICsale.FHeadselfi0453,ICsale.FHeadselfi0464,t_Organization.Fname