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
,t_RP_NewCheckInfo.FcheckamountFor,t_RP_NewCheckInfo.fcheckdate)t
group by [客户名称 (Client)],[发票号码 (Inv. No)]
[解决办法]
Select
Sum([ 货物净值 (Net value)]) As [ 货物净值 (Net value)],
Sum([税 额 (VAT)]) AS [税 额 (VAT)],
Sum([ 运 费 (Transport cost)]) As [ 运 费 (Transport cost)],
Sum([ 价税合计 (Total Amount)]) as [ 价税合计 (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,
t_RP_NewCheckInfo.FcheckamountFor,
t_RP_NewCheckInfo.fcheckdate
) A
Group By
[客户名称 (Client)],
[发票号码 (Inv. No)]
