SQL语句中关于空记录sum求和返回null值的处理,兼谈case的处理

最新推荐文章于 2024-05-06 17:27:39 发布

置顶

lifuyun

最新推荐文章于 2024-05-06 17:27:39 发布

阅读量4.8k

点赞数

分类专栏： SQLServer 文章标签： null sql sql server input join table

本文链接：https://blog.csdn.net/lifuyun/article/details/2444859

版权

在SQL查询中遇到空值求和返回NULL的问题，通过使用CASE函数进行条件判断，将NULL转换为0，从而解决求和问题。文章详细介绍了CASE函数的用法，并给出了具体示例。

摘要由CSDN通过智能技术生成

今天做项目写SQL语句，写了好多，如下：

SELECT     t0.DRAI_PIPE_NAME_ROAD, t0.DRAI_PIPE_BROAD_NAME, t0.DRAI_PIPE_EROAD_NAME, t1.w1 + t2.w2 + t3.w3 + t4.w4 AS sumfour, t1.w1, t2.w2, t3.w3,
                      t4.w4
FROM         (SELECT     TOP (100) PERCENT DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME
                       FROM          sde.TDRAI_PIPE_ST
                       WHERE      (DRAI_PIPE_NAME_ROAD IN
                                                  (SELECT DISTINCT DRAI_PIPE_NAME_ROAD
                                                    FROM          sde.TDRAI_PIPE_ST AS TDRAI_PIPE_ST_1))
                       GROUP BY DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME
                       ORDER BY DRAI_PIPE_NAME_ROAD) AS t0 LEFT OUTER JOIN
                          (SELECT     TOP (100) PERCENT DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME, SUM(DRAI_PIPE_LENGTH)
                                                   AS w1
                            FROM          sde.TDRAI_PIPE_ST AS TDRAI_PIPE_ST_2
                            WHERE      (DRAI_PIPE_D1 >= 150) AND (DRAI_PIPE_D1 <= 530)
                            GROUP BY DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME
                            ORDER BY DRAI_PIPE_NAME_ROAD) AS t1 ON t0.DRAI_PIPE_NAME_ROAD = t1.DRAI_PIPE_NAME_ROAD AND
                      t0.DRAI_PIPE_BROAD_NAME = t1.DRAI_PIPE_BROAD_NAME AND t0.DRAI_PIPE_EROAD_NAME = t1.DRAI_PIPE_EROAD_NAME LEFT OUTER JOIN
                          (SELECT     TOP (100) PERCENT DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME, SUM(DRAI_PIPE_LENGTH)
                                                   AS w2
                            FROM          sde.TDRAI_PIPE_ST AS TDRAI_PIPE_ST_5
                            WHERE      (DRAI_PIPE_D1 >= 600) AND (DRAI_PIPE_D1 <= 1000)
                            GROUP BY DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME
                            ORDER BY DRAI_PIPE_NAME_ROAD) AS t2 ON t0.DRAI_PIPE_NAME_ROAD = t2.DRAI_PIPE_NAME_ROAD AND
                      t0.DRAI_PIPE_BROAD_NAME = t2.DRAI_PIPE_BROAD_NAME AND t0.DRAI_PIPE_EROAD_NAME = t2.DRAI_PIPE_EROAD_NAME LEFT OUTER JOIN
                          (SELECT     TOP (100) PERCENT DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME, SUM(DRAI_PIPE_LENGTH)
                                                   AS w3
                            FROM          sde.TDRAI_PIPE_ST AS TDRAI_PIPE_ST_4
                            WHERE      (DRAI_PIPE_D1 >= 1050) AND (DRAI_PIPE_D1 <= 1400)
                            GROUP BY DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME
                            ORDER BY DRAI_PIPE_NAME_ROAD) AS t3 ON t0.DRAI_PIPE_NAME_ROAD = t3.DRAI_PIPE_NAME_ROAD AND
                      t0.DRAI_PIPE_BROAD_NAME = t3.DRAI_PIPE_BROAD_NAME AND t0.DRAI_PIPE_EROAD_NAME = t3.DRAI_PIPE_EROAD_NAME LEFT OUTER JOIN
                          (SELECT     TOP (100) PERCENT DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME, SUM(DRAI_PIPE_LENGTH)
                                                   AS w4
                            FROM          sde.TDRAI_PIPE_ST AS TDRAI_PIPE_ST_3
                            WHERE      (DRAI_PIPE_D1 >= 1500)
                            GROUP BY DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME
                            ORDER BY DRAI_PIPE_NAME_ROAD) AS t4 ON t0.DRAI_PIPE_NAME_ROAD = t4.DRAI_PIPE_NAME_ROAD AND
                      t0.DRAI_PIPE_BROAD_NAME = t4.DRAI_PIPE_BROAD_NAME AND t0.DRAI_PIPE_EROAD_NAME = t4.DRAI_PIPE_EROAD_NAME

可是出现这样的情况：求和时有NULL的和值为NULL了！

经过这样的处理：

SELECT     t0.DRAI_PIPE_NAME_ROAD, t0.DRAI_PIPE_BROAD_NAME, t0.DRAI_PIPE_EROAD_NAME, CASE WHEN t1.w1 IS NULL
                      THEN 0.0 ELSE t1.w1 END + CASE WHEN t2.w2 IS NULL THEN 0.0 ELSE t2.w2 END + CASE WHEN t3.w3 IS NULL
                      THEN 0.0 ELSE t3.w3 END + CASE WHEN t4.w4 IS NULL THEN 0.0 ELSE t4.w4 END AS sumfour, t1.w1, t2.w2, t3.w3, t4.w4
FROM         (SELECT     TOP (100) PERCENT DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME
                       FROM          sde.TDRAI_PIPE_ST
                       WHERE      (DRAI_PIPE_NAME_ROAD IN
                                                  (SELECT DISTINCT DRAI_PIPE_NAME_ROAD
                                                    FROM          sde.TDRAI_PIPE_ST AS TDRAI_PIPE_ST_1))
                       GROUP BY DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME
                       ORDER BY DRAI_PIPE_NAME_ROAD) AS t0 LEFT OUTER JOIN
                          (SELECT     TOP (100) PERCENT DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME, SUM(DRAI_PIPE_LENGTH)
                                                   AS w1
                            FROM          sde.TDRAI_PIPE_ST AS TDRAI_PIPE_ST_2
                            WHERE      (DRAI_PIPE_D1 >= 150) AND (DRAI_PIPE_D1 <= 530)
                            GROUP BY DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME
                            ORDER BY DRAI_PIPE_NAME_ROAD) AS t1 ON t0.DRAI_PIPE_NAME_ROAD = t1.DRAI_PIPE_NAME_ROAD AND
                      t0.DRAI_PIPE_BROAD_NAME = t1.DRAI_PIPE_BROAD_NAME AND t0.DRAI_PIPE_EROAD_NAME = t1.DRAI_PIPE_EROAD_NAME LEFT OUTER JOIN
                          (SELECT     TOP (100) PERCENT DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME, SUM(DRAI_PIPE_LENGTH)
                                                   AS w2
                            FROM          sde.TDRAI_PIPE_ST AS TDRAI_PIPE_ST_5
                            WHERE      (DRAI_PIPE_D1 >= 600) AND (DRAI_PIPE_D1 <= 1000)
                            GROUP BY DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME
                            ORDER BY DRAI_PIPE_NAME_ROAD) AS t2 ON t0.DRAI_PIPE_NAME_ROAD = t2.DRAI_PIPE_NAME_ROAD AND
                      t0.DRAI_PIPE_BROAD_NAME = t2.DRAI_PIPE_BROAD_NAME AND t0.DRAI_PIPE_EROAD_NAME = t2.DRAI_PIPE_EROAD_NAME LEFT OUTER JOIN
                          (SELECT     TOP (100) PERCENT DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME, SUM(DRAI_PIPE_LENGTH)
                                                   AS w3
                            FROM          sde.TDRAI_PIPE_ST AS TDRAI_PIPE_ST_4
                            WHERE      (DRAI_PIPE_D1 >= 1050) AND (DRAI_PIPE_D1 <= 1400)
                            GROUP BY DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME
                            ORDER BY DRAI_PIPE_NAME_ROAD) AS t3 ON t0.DRAI_PIPE_NAME_ROAD = t3.DRAI_PIPE_NAME_ROAD AND
                      t0.DRAI_PIPE_BROAD_NAME = t3.DRAI_PIPE_BROAD_NAME AND t0.DRAI_PIPE_EROAD_NAME = t3.DRAI_PIPE_EROAD_NAME LEFT OUTER JOIN
                          (SELECT     TOP (100) PERCENT DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME, SUM(DRAI_PIPE_LENGTH)
                                                   AS w4
                            FROM          sde.TDRAI_PIPE_ST AS TDRAI_PIPE_ST_3
                            WHERE      (DRAI_PIPE_D1 >= 1500)
                            GROUP BY DRAI_PIPE_NAME_ROAD, DRAI_PIPE_BROAD_NAME, DRAI_PIPE_EROAD_NAME
                            ORDER BY DRAI_PIPE_NAME_ROAD) AS t4 ON t0.DRAI_PIPE_NAME_ROAD = t4.DRAI_PIPE_NAME_ROAD AND
                      t0.DRAI_PIPE_BROAD_NAME = t4.DRAI_PIPE_BROAD_NAME AND t0.DRAI_PIPE_EROAD_NAME = t4.DRAI_PIPE_EROAD_NAME

这样就可以了！

在网上找来找去找到这样的示例：

记录相加则得到的值为NULL。下面分析以下我下面的SQL语句

select xmno,( select xmbm from xmb where no = a.xmno) as xmbm,
2

( select xmmc from xmb where no = a.xmno) as xmmc,( select jdmc from jdb where jdb.jddm = ( select jddm from xmb where no = a.xmno)) as jdmc,( select sj1 from xmb where no = a.xmno) as sj1,
3

( select sj2 from xmb where no = a.xmno) as sj2,
4

( sum (f011 + f012 + f013 + f014 + f021 + f022 + f023 + f024 + f031 + f032 + f033 + f034 + f041 + f042 + f043 + f044 + f051 + f052 + f053 + f054 + f061 + f062 + f063 + f064 + f071 + f072 + f073 + f074 + f081 + f082 + f083 + f084 + f091 + f092 + f093 + f094 + f101 + f102 + f103 + f104 + f111 + f112 + f113 + f114 + f121 + f122 +