SQL 的写法？
select
ID = Identity(Int, 1, 1),
D.FID AS FID,
A.finterid AS finterid,
C.Fname as [客户名称 (Client)],
A.Fbillno as [发票号码 (Inv. No)],
A.Fdate as [发票日期 (Inv. Date)],
Sum(B.FAmount) AS [ 货物净值 (Net value)],
Sum(B.FTaxAmount) AS [税 额 (VAT)],
A.FHeadselfi0453 as [ 运 费 (Transport cost)],
sum(B.FAmountincludetax) as [ 价税合计 (Total Amount)],
A.FHeadselfi0464 as [应回款日 (Due date)],
D.FcheckamountFor as [实际回款金额 (Amount cashed)] ,
D.fcheckdate as [实际回款日期 (Date of cash)]
into #ghtemp
from
ICsale A
JOIN
ICsaleentry B
on A.finterid =B.finterid
JOIN
t_Organization C
on A.FcustID =C.FitemID
left join
t_RP_NewCheckInfo D
on A.finterid=D.Fbillid and D.ftype=3
GROUP BY
A.FInterID,
A.Fbillno,
A.Fdate,
A.FHeadselfi0453,
A.FHeadselfi0464,
C.Fname,
D.FcheckamountFor,
D.fcheckdate,
D.FID
select E.finterid,E.fid as fid,f.fdate as fdate
into #ghtemp1
from #ghtemp
JOIN
t_RP_NewCheckInfo E
on #ghtemp.fid=E.FID AND E.ftype=5
JOIN
t_RP_NewReceiveBill F
on e.Fbillid=f.Fbillid
update #ghtemp set [实际回款日期 (Date of cash)] = #ghtemp1.fdate from #ghtemp1 where #ghtemp.fid=#ghtemp1.fid
select * from #ghtemp
Union All
Select
NULL,
Null,
Null,
'bb ',
Null,
Null,
Null,
Null,
Null,
Sum([ 价税合计 (Total Amount)]) as [ 价税合计 (Total Amount)],
Null,
Sum([实际回款金额 (Amount cashed)]) as [实际回款金额 (Amount cashed)],
Null
From
(
select (Case When Exists (Select ID From #ghtemp Where [发票号码 (Inv. No)] = A.[发票号码 (Inv. No)] And ID < A.ID) Then 0 Else [ 价税合计 (Total Amount)] End) As [ 价税合计 (Total Amount)], [实际回款金额 (Amount cashed)] from #ghtemp A) B
Drop Table #ghtemp1
Drop Table #ghtemp
在SQL查询分析器里执行没有问题。在金蝶软件里执行有问题（into #ghtemp 出错）不用临时表，如何写以上SQL。
[解决办法]
1.把临时表改成实体表试试
2.如果是金碟的不支持into的话,就先
create table ghtemp(id int identity(1,1),FID int,...)和第一段select里字段结构一样的表,再
insert into ghtemp(fid,...)
select
D.FID AS FID,
A.finterid AS finterid,
C.Fname as [客户名称 (Client)],
A.Fbillno as [发票号码 (Inv. No)],
A.Fdate as [发票日期 (Inv. Date)],
Sum(B.FAmount) AS [ 货物净值 (Net value)],
Sum(B.FTaxAmount) AS [税 额 (VAT)],
A.FHeadselfi0453 as [ 运 费 (Transport cost)],
sum(B.FAmountincludetax) as [ 价税合计 (Total Amount)],
A.FHeadselfi0464 as [应回款日 (Due date)],
D.FcheckamountFor as [实际回款金额 (Amount cashed)] ,
D.fcheckdate as [实际回款日期 (Date of cash)]
from
ICsale A
JOIN
ICsaleentry B
on A.finterid =B.finterid
JOIN
t_Organization C
on A.FcustID =C.FitemID
left join
t_RP_NewCheckInfo D
on A.finterid=D.Fbillid and D.ftype=3
下面的#ghtemp1也同样改成实体表
最后再将drop两个表
[解决办法]
应该是用户没有访问tempdb数据库的权限吧
[解决办法]
--如果每個[发票号码 (Inv. No)]的[ 价税合计 (Total Amount)]是一樣的話, 可以不用建臨時表, 這麼試試
Select
E.*,
F.fdate As [实际回款日期 (Date of cash)]
From
(
select
D.FID AS FID,
A.finterid AS finterid,
C.Fname as [客户名称 (Client)],
A.Fbillno as [发票号码 (Inv. No)],
A.Fdate as [发票日期 (Inv. Date)],
Sum(B.FAmount) AS [ 货物净值 (Net value)],
Sum(B.FTaxAmount) AS [税 额 (VAT)],
A.FHeadselfi0453 as [ 运 费 (Transport cost)],
sum(B.FAmountincludetax) as [ 价税合计 (Total Amount)],
A.FHeadselfi0464 as [应回款日 (Due date)],
D.FcheckamountFor as [实际回款金额 (Amount cashed)]
from
ICsale A
JOIN
ICsaleentry B
on A.finterid =B.finterid
JOIN
t_Organization C
on A.FcustID =C.FitemID
left join
t_RP_NewCheckInfo D
on A.finterid=D.Fbillid and D.ftype=3
GROUP BY
A.FInterID,
A.Fbillno,
A.Fdate,
A.FHeadselfi0453,
A.FHeadselfi0464,
C.Fname,
D.FcheckamountFor,
D.fcheckdate,
D.FID ) E
Inner Join
t_RP_NewCheckInfo F
on E.fid=F.FID AND F.ftype=5
Inner JOIN
t_RP_NewReceiveBill G
on F.Fbillid = G.Fbillid
Union All
Select
Null,
[客户名称 (Client)],
[发票号码 (Inv. No)],
Null,
Null,
Null,
Null,
Sum(Distinct [ 价税合计 (Total Amount)]) as [ 价税合计 (Total Amount)],
Null,
Sum([实际回款金额 (Amount cashed)]) as [实际回款金额 (Amount cashed)],
Null
From
(
select
D.FID AS FID,
A.finterid AS finterid,
C.Fname as [客户名称 (Client)],
A.Fbillno as [发票号码 (Inv. No)],
A.Fdate as [发票日期 (Inv. Date)],
Sum(B.FAmount) AS [ 货物净值 (Net value)],
Sum(B.FTaxAmount) AS [税 额 (VAT)],
A.FHeadselfi0453 as [ 运 费 (Transport cost)],
sum(B.FAmountincludetax) as [ 价税合计 (Total Amount)],
A.FHeadselfi0464 as [应回款日 (Due date)],
D.FcheckamountFor as [实际回款金额 (Amount cashed)]
from
ICsale A
JOIN
ICsaleentry B
on A.finterid =B.finterid
JOIN
t_Organization C
on A.FcustID =C.FitemID
left join
t_RP_NewCheckInfo D
on A.finterid=D.Fbillid and D.ftype=3
GROUP BY
A.FInterID,
A.Fbillno,
A.Fdate,
A.FHeadselfi0453,
A.FHeadselfi0464,
C.Fname,
D.FcheckamountFor,
D.fcheckdate,
D.FID
) A
Group By
[客户名称 (Client)],
[发票号码 (Inv. No)]
[解决办法]
楼主为什么不可以把它们换成真的表呢，只要先把它们的数据清空就可以了.
也可以用表变量
还可以把它们写成一个proc
