SQL sum没有查询到数据的时候能不能显示为0

[gaomeng320]

SELECT  cc = ISNULL(SUM(ISNULL(DispatchMQ, 0)),0)
FROM    dbo.T_BASE_TDispatchNotice
WHERE   DeleteFlag = 0
        AND TPSN = 'YKJH201612230000'
GROUP BY TPSN

想实现在没有数据的时候这列有值且为0

[gaomeng320]

引用 4 楼 closurer 的回复:

[quote=引用 3 楼 closurer 的回复:]

select ISNULL(cc, 0) from 
(
SELECT  cc = SUM(ISNULL(DispatchMQ, 0)),0)
FROM    dbo.T_BASE_TDispatchNotice
WHERE   DeleteFlag = 0
        AND TPSN = 'YKJH201612230000'
GROUP BY TPSN
) a

更正一下：

select ISNULL(cc, 0) from 
(
SELECT  cc = SUM(ISNULL(DispatchMQ, 0))
FROM    dbo.T_BASE_TDispatchNotice
WHERE   DeleteFlag = 0
        AND TPSN = 'YKJH201612230000'
GROUP BY TPSN
) a

[/quote] 不行，还是没有显示

[gaomeng320]

引用 2 楼 Chinajiyong 的回复:

用COALESCE关键字 SELECT COALESCE(SUM(name),0) FROM person WHERE id > 0

也不行~

[gaomeng320]

我错了， 我加group by干嘛 我不用加他的 然后就可以了，谢谢以上~

[闭包客]

引用 5 楼 gaomeng320 的回复:

[quote=引用 4 楼 closurer 的回复:] [quote=引用 3 楼 closurer 的回复:]

select ISNULL(cc, 0) from 
(
SELECT  cc = SUM(ISNULL(DispatchMQ, 0)),0)
FROM    dbo.T_BASE_TDispatchNotice
WHERE   DeleteFlag = 0
        AND TPSN = 'YKJH201612230000'
GROUP BY TPSN
) a

更正一下：

select ISNULL(cc, 0) from 
(
SELECT  cc = SUM(ISNULL(DispatchMQ, 0))
FROM    dbo.T_BASE_TDispatchNotice
WHERE   DeleteFlag = 0
        AND TPSN = 'YKJH201612230000'
GROUP BY TPSN
) a

[/quote] 不行，还是没有显示[/quote] 抱歉！ 我刚才测试了一下，问题在于你的 group by 从句。 有 group by 从句的情况下，对 0行的结果集求和，是不会返回 null 的。 应改为：

select ISNULL(sum(cc), 0) from 
(
SELECT  cc = SUM(ISNULL(DispatchMQ, 0))
FROM    dbo.T_BASE_TDispatchNotice
WHERE   DeleteFlag = 0
        AND TPSN = 'YKJH201612230000'
GROUP BY TPSN
) a

[闭包客]

不过这样写即使能达到目的，也是一个糟糕的 SQL 语句。 它的解决方法比较曲折，可读性不佳。

[gaomeng320]

引用 9 楼 closurer 的回复:

不过这样写即使能达到目的，也是一个糟糕的 SQL 语句。 它的解决方法比较曲折，可读性不佳。

好的，非常感谢

[EnForGrass]

用COALESCE关键字 SELECT COALESCE(SUM(name),0) FROM person WHERE id > 0

[闭包客]

select ISNULL(cc, 0) from 
(
SELECT  cc = SUM(ISNULL(DispatchMQ, 0)),0)
FROM    dbo.T_BASE_TDispatchNotice
WHERE   DeleteFlag = 0
        AND TPSN = 'YKJH201612230000'
GROUP BY TPSN
) a

[闭包客]

引用 3 楼 closurer 的回复:

select ISNULL(cc, 0) from 
(
SELECT  cc = SUM(ISNULL(DispatchMQ, 0)),0)
FROM    dbo.T_BASE_TDispatchNotice
WHERE   DeleteFlag = 0
        AND TPSN = 'YKJH201612230000'
GROUP BY TPSN
) a

更正一下：

select ISNULL(cc, 0) from 
(
SELECT  cc = SUM(ISNULL(DispatchMQ, 0))
FROM    dbo.T_BASE_TDispatchNotice
WHERE   DeleteFlag = 0
        AND TPSN = 'YKJH201612230000'
GROUP BY TPSN
) a