--给表A加个索引
CREATE NONCLUSTERED INDEX IX_表A_客户
ON 表A (客户)
INCLUDE (金额);
--你的查询不完整吧
select c.客户,
c.客户初欠+a.金额
from 客户表 c
LEFT JOIN ( SELECT 客户,
SUM(金额) 金额
FROM 表A
GROUP BY 客户
) a
ON a.客户 = c.客户
CREATE NONCLUSTERED INDEX IX_表A_客户
ON 表A (客户)
INCLUDE (金额);
--你的查询不完整吧
select c.客户,
c.客户初欠+a.金额
from 客户表 c
LEFT JOIN ( SELECT 客户,
SUM(金额) 金额
FROM 表A
GROUP BY 客户
) a
ON a.客户 = c.客户
--getSS 函数是取客户收款
--getYS 函数是取客户货款
CREATE function getYS
(
@cCusCode varchar(50)
)
returns float
as
begin
declare @YS floatselect @YS=convert(decimal(18,2),isnull(sum(iQuantity*cInvUnitCost),0)) from RdRecords where cCusCode=@cCusCode and isnull(cInvFahuoDate,'')<>''
return @YS
end
下面是查询语句,特慢,我想有偿寻求解决。
select ID,cCusCode, cCusAbbName,iMoney,dbo.getYS(cCusCode) as YSiMoney,dbo.getSS(cCusCode) as SSiMoney,(iMoney-dbo.getYS(cCusCode)+dbo.getSS(cCusCode)) as YE, from Customer
WITH YS AS (
SELECT ccuscode,
CONVERT(DECIMAL(18,2),
SUM(iquantity* cinvunitcost)
) ysimoney
FROM rdrecords
WHERE Isnull(cinvfahuodate,'') <> ''
GROUP BY ccuscode
),
SS AS (
...
)
SELECT id,
ccuscode,
ccusabbname,
imoney,
ys.ysimoney,
ss.ssimoney,
(imoney - ISNULL(ys.ysimoney,0) + ISNULL(ss.ssimoney,0)) AS ye
FROM customer
LEFT JOIN YS ON ys.ccuscode = customer.ccuscode
LEFT JOIN SS ON ss.ccuscode = customer.ccuscode
用 WITH 语句只不过分段清晰点。