一条SQL求解，在线等

XinJW 2013-05-26 05:46:14

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。

...全文

192 6 打赏收藏转发到动态举报

写回复

用AI写文章

6 条回复

切换为时间正序

请发表友善的回复…

发表回复

MrYangkang 2013-05-27

打赏
举报

-- sum(isnull(字段名,0))

ahhfql 2013-05-27

打赏
举报

学习

撸大湿 2013-05-26

打赏
举报

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

中关村网名 2013-05-26

打赏
举报

那在可能出现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

XinJW 2013-05-26

打赏
举报

引用 1 楼 tntzbzc 的回复:

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

老大，你的做法是对的，但是结果不对。我表达不清楚，不好意思。我重新整理一下，我要的结果。 nBuy.Price是消费（也就是再次消费）过的。可能这里是没有二次消费，所以结果会是NULL， nMoney.Money就是付款的记录，也可能是一分钱都没交，全部欠着，结果也是NULL， nCustom.Receivable是基本的消费款项，这个不可能是NULL 拿第二第记录为例：Series其实就是Receivable的内容，n_totalSpending就是总消费项，n_totalPaying是总付款项，n_Arrearge就是欠款。

撸大湿 2013-05-26

打赏
举报

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