使用PIVOT子句执行SQL查询时如何将返回的NULL值替换为0

你遇到的NULL值是PIVOT操作生成的分类列返回的，你之前写的ISNULL仅处理了PIVOT之前的atm字段，没有覆盖到PIVOT生成的[RURAL]、[SEMI URBAN]、[URBAN]三个列——当某行没有对应类别的数据时，PIVOT的sum聚合会返回NULL，只要在最外层查询对这三个列单独做NULL转换即可。

修改后的完整SQL如下：

WITH MAIN AS (
SELECT CATEGORY,LBRCODE,STATECODE,DISTCODE,BLOCKCODE,ATMYN X  FROM Sheet1$ 
UNION 
SELECT CATEGORY,LBRCODE,STATECODE,DISTCODE,BLOCKCODE,MOBILEVAN  X FROM Sheet1$ WHERE MOBILEVAN='Y'
)
,
BLOCK AS (
SELECT * FROM Sheet1$
)
SELECT 
    STATECODE,
    DISTCODE,
    BLOCKCODE,
    ISNULL([RURAL], 0) AS [RURAL],
    ISNULL([SEMI URBAN], 0) AS [SEMI URBAN],
    ISNULL([URBAN], 0) AS [URBAN]
FROM (
    SELECT STATECODE,DISTCODE,BLOCKCODE,CATEGORY,ISNULL(atm,0) atm FROM (
        SELECT a.STATECODE,a.DISTCODE,a.BLOCKCODE,a.CATEGORY,
        sum(CASE WHEN b.X ='Y' THEN 1 ELSE 0 END) atm
        FROM BLOCK a LEFT JOIN MAIN b ON a.LBRCODE=b.LBRCODE
        GROUP BY a.STATECODE,a.DISTCODE,a.BLOCKCODE,a.CATEGORY
    ) x
) y
pivot ( sum(atm) FOR CATEGORY  IN ( [RURAL] , [SEMI URBAN] , [URBAN]) 
) a

如果你使用的是MySQL这类不支持ISNULL的数据库，将ISNULL替换为IFNULL即可，语法逻辑完全一致。

内容的提问来源于stack exchange，提问作者hemu_k