一条SQL求解

2013-06-19

一条SQL求解，在线等这里面的NULL值是在计算的时候产生的，怎么样在计算的时候让NULL值为0比如 nCustom.Rece

一条SQL求解，在线等

这里面的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

        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的地方都加上Isnull函数不就可以了吗？不知道我理解的对不对。

SELECT nCustom.CustomNO, nCustom.M_Name, nCustom.F_Name, nCustom.Series, 
      nCustom.CardNO, nCustom.InReturnMoney, nCustom.Sales, 
      ISNULL(nCustom.Receivable,0) +ISNULL( nBuy.Price,0) - ISNULL(nMoney.Money,0) AS n_Arrearage, 
      ISNULL(nCustom.Receivable,0) + ISNULL(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
        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

[解决办法]

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, ISNULL(SUM(Price),0) 'Price'
         FROM [CustomDetails]
         GROUP BY CustomSN) nBuy ON
      nCustom.CustomNO = nBuy.CustomSN LEFT OUTER JOIN
          (SELECT CustomNO, ISNULL(SUM(Money),0) 'Money'
         FROM [MONEY]
         GROUP BY CustomNO) nMoney ON nCustom.CustomNO = nMoney.CustomNO

热点排行

SQL Server

一条SQL求解