SQL中SELECT中列SUM运算后为null

最新推荐文章于 2024-05-07 10:49:04 发布

五歲抬頭的我

最新推荐文章于 2024-05-07 10:49:04 发布

阅读量1k

点赞数 1

分类专栏：聚合函数文章标签： sql 数据库 mysql sqlserver

本文链接：https://blog.csdn.net/qq_33763772/article/details/130882724

版权

聚合函数专栏收录该内容

1 篇文章 0 订阅

订阅专栏

列上做运算时,SUM后的数值一旦为null,那么相加后的值就是null,导致运算后错误,
此时就需要ISNULL函数（MySQL是IFNULL）
对比：

只用SUM函数：
SUM(a)=1.5,
SUM(b)=null,
SUM(a)+SUM(b) = null 

用函数ISNULL函数后:
ISNULL(SUM(a), 0) = 1.5
ISNULL(SUM(b), 0) = 0
ISNULL(SUM(a), 0) + ISNULL(SUM(b), 0) = 1.5

以下是我自己的SQL案例


SELECT IC.CustNo,
       IC.AccountStatus,
       IC.CustomerName,
       SD.ItemName,
       tb0.CountCard                                                                  CountCard,
       SUM(IARMB.Balance)                                                             AccountBalance,
       SUM(tb0.ReserveFund)                                                           ReserveFund,
       SUM(tb0.Balance)                                                               CardBalance,
       ISNULL(SUM(tb1.sumMoney), 0) + ISNULL(SUM(tb1.sumRefundProfit), 0)             RfuStore,
       ISNULL(SUM(tb2.sumRevokeMoney), 0) + ISNULL(SUM(tb2.sumRevokeRefundProfit), 0) RfuStoreRevoke,
       SUM(tb3.sumRefundMoney)                                                        SumRefundMoney,
       ISNULL(SUM(tb5.sumMoney), 0) + ISNULL(SUM(tb5.sumRefundProfit), 0)             PaymentOfArrears,
       ISNULL(SUM(tb5.sumMoney), 0) + ISNULL(SUM(tb5.sumRefundProfit), 0) +
       ISNULL(SUM(tb1.sumMoney), 0) + ISNULL(SUM(tb1.sumRefundProfit), 0) +
       ISNULL(SUM(tb2.sumRevokeMoney), 0) + ISNULL(SUM(tb2.sumRevokeRefundProfit), 0) RealStoreMoney,
       SUM(tb4.sumQty)                                                                SumQty,
       SUM(tb4.sumActualMoney)                                                        SumActualMoney
FROM IC_Cust IC
         LEFT JOIN IC_AccountRFUMoneyBal IARMB ON IC.CustNo = IARMB.CustNo
         LEFT JOIN SysDic SD ON SD.ItemType = 6 AND SD.ItemId = IC.AccountStatus
         LEFT JOIN (SELECT COUNT(1) CountCard, IUC.CustNo, SUM(IUCB.ReserveFund) ReserveFund, SUM(IUCB.Balance) Balance
                    FROM IC_UserCard IUC
                             LEFT JOIN IC_UserCardBal IUCB ON IUCB.CardNo = IUC.CardNo
                    WHERE 1 = 1
                      AND IUC.CardStatus IN (2, 4)
                    GROUP BY IUC.CustNo) tb0 ON tb0.CustNo = IC.CustNo
         LEFT JOIN (SELECT IC.CustNo, SUM(IARMSD.Money) sumMoney, SUM(IARMSD.RefundProfit) sumRefundProfit
                    FROM IC_Cust IC
                             LEFT JOIN IC_AccountRFUMoneyStoreDtl IARMSD
                                       ON IC.CustNo = IARMSD.CustNo AND IARMSD.OptType = 17
                    WHERE 1 = 1
                      AND IARMSD.OptTime BETWEEN '2023-01-01 00:00:00' and '2023-03-31 23:59:59'
                    GROUP BY IC.CustNo) tb1 ON tb1.CustNo = IC.CustNo
         LEFT JOIN (SELECT IC.CustNo,
                           SUM(IARMSD2.Money)        sumRevokeMoney,
                           SUM(IARMSD2.RefundProfit) sumRevokeRefundProfit
                    FROM IC_Cust IC
                             LEFT JOIN IC_AccountRFUMoneyStoreDtl IARMSD2
                                       ON IC.CustNo = IARMSD2.CustNo AND IARMSD2.OptType = 18
                    WHERE 1 = 1
                      AND IARMSD2.OptTime BETWEEN '2023-01-01 00:00:00' and '2023-03-31 23:59:59'
                    GROUP BY IC.CustNo) tb2 ON tb2.CustNo = IC.CustNo
         LEFT JOIN (SELECT IC.CustNo, SUM(IARMSD3.Money) sumRefundMoney
                    FROM IC_Cust IC
                             LEFT JOIN IC_AccountRFUMoneyStoreDtl IARMSD3
                                       ON IC.CustNo = IARMSD3.CustNo AND IARMSD3.OptType = 21
                    WHERE 1 = 1
                      AND IARMSD3.OptTime BETWEEN '2023-01-01 00:00:00' and '2023-03-31 23:59:59'
                    GROUP BY IC.CustNo) tb3 ON tb3.CustNo = IC.CustNo
         LEFT JOIN (SELECT IC.CustNo, SUM(IARMSD.Money) sumMoney, SUM(IARMSD.RefundProfit) sumRefundProfit
                    FROM IC_Cust IC
                             LEFT JOIN IC_AccountRFUMoneyStoreDtl IARMSD
                                       ON IC.CustNo = IARMSD.CustNo AND IARMSD.OptType = 20
                    WHERE 1 = 1
                      AND IARMSD.OptTime BETWEEN '2023-01-01 00:00:00' and '2023-03-31 23:59:59'
                    GROUP BY IC.CustNo) tb5 ON tb5.CustNo = IC.CustNo

         LEFT JOIN (SELECT IC.CustNo, SUM(TOT.Qty) sumQty, SUM(TOT.ActualMoney) sumActualMoney
                    FROM IC_Cust IC
                             LEFT JOIN T_OilTrade TOT ON TOT.CustNo = IC.CustNo
                    WHERE 1 = 1
                      AND TOT.PayTime BETWEEN '2023-01-01 00:00:00' and '2023-03-31 23:59:59'
                    GROUP BY IC.CustNo) tb4 ON tb4.CustNo = IC.CustNo
WHERE 1 = 1
--   AND IC.CustNo = '000152940580'
GROUP BY IC.CustNo, IARMB.Balance, IC.AccountStatus, IC.CustomerName,
         SD.ItemName, tb0.CountCard