SELECT nCustom.CustomNO, nCustom.M_Name, nCustom.F_Name, nCustom.Series,
nCustom.CardNO, nCustom.InReturnMoney, nCustom.Sales,
nCustom.Receivable + nBuy.Price - nMoney.Money AS n_Arrearage,
nCustom.Receivable + nBuy.Price AS n_TotalSpending,
nMoney.Money AS n_TotalPaying
FROM (SELECT CustomNO, SUM(Receivable) 'Receivable', M_Name, F_Name, SeriesType,
M_Mobile, F_Mobile, M_Address, F_Address, Series, CardNO, InReturnMoney,
Sales
FROM [Custom]
GROUP BY CustomNO, M_Name, F_name, SeriesType, M_Mobile, F_Mobile,
M_Address, F_Address, Series, CardNO, InReturnMoney, Sales)
nCustom LEFT OUTER JOIN
(SELECT CustomSN, SUM(Price) 'Price'
FROM [CustomDetails]
GROUP BY CustomSN) nBuy ON
nCustom.CustomNO = nBuy.CustomSN LEFT OUTER JOIN
(SELECT CustomNO, SUM(Money) 'Money'
FROM [MONEY]
GROUP BY CustomNO) nMoney ON nCustom.CustomNO = nMoney.CustomNO
得出结果如下：
这里面的NULL值是在计算的时候产生的，怎么样在计算的时候让NULL值为0比如 nCustom.Receivable + nBuy.Price - nMoney.Money AS n_Arrearage,
nbuy.price里面没有数据的时候是0而不是NULL。
------解决方案--------------------
SELECT nCustom.CustomNO, nCustom.M_Name, nCustom.F_Name, nCustom.Series,
nCustom.CardNO, nCustom.InReturnMoney, nCustom.Sales,
ISNULL(nCustom.Receivable + nBuy.Price - nMoney.Money,0) AS n_Arrearage,
ISNULL(nCustom.Receivable + nBuy.Price,0) AS n_TotalSpending,
ISNULL(nMoney.Money,0) AS n_TotalPaying
FROM (SELECT CustomNO, SUM(Receivable) 'Receivable', M_Name, F_Name, SeriesType,
M_Mobile, F_Mobile, M_Address, F_Address, Series, CardNO, InReturnMoney,
Sales