时间戳

首页 > 解决方案 > SQL Server SUM 返回 null，它应该返回 0

sql - SQL Server SUM 返回 null，它应该返回 0

问题描述

如果列为空，我的查询将返回 null。它应该返回 0，以便我可以在我的代码中计算它。

我在网上搜索并发现我正在使用 SQL Server 2012 的“COALESCE”和“ISNULL”我应该使用什么？我尝试了 ISNULL 并且它在我的查询中不起作用。这是我的代码。

 SqlCommand cmd2 = new SqlCommand("SELECT ISNULL(SUM (Male) as Male, SUM(Female) as Female ,SUM(Pax) as Pax,SUM(Single) as Single,SUM(Married) as Married,SUM(Students) as Students,SUM(Elementary) as Elementary,SUM(Highschool) as Highschool,SUM(College) as College,SUM(PWD) as PWD, SUM([AR Users]) as ARUsers,SUM([12 Below]) as age1,SUM([13-21]) as age2,SUM([22-35]) as age3,SUM([36-50]) as age4,SUM([51-65]) as age5,SUM([65 Above]) as age6 ,0) FROM  [tbl_ForeignVisit] where [Date Added] >= '"
            + dateTimePicker1.Value.Date.ToShortDateString() + "' AND  [Date Added] <='" + dateTimePicker2.Value.Date.ToShortDateString() + "'  ;", connection);

标签： sqlsql-server

解决方案

我认为您需要为每个列使用ISNULL或，而不仅仅是一个。COALESCESUM

并且您的 SQL 查询字符串存在 SQL 注入问题，请使用参数而不是 Connect SQL 语法

string sqlQuery = @"SELECT 
    ISNULL(SUM(Male),0) as Male, 
    ISNULL(SUM(Female),0)  as Female ,
    ISNULL(SUM(Pax),0)  as Pax,
    ISNULL(SUM(Single),0)  as Single,
    ISNULL(SUM(Married),0)  as Married,
    ISNULL(SUM(Students),0)  as Students,
    ISNULL(SUM(Elementary),0)  as Elementary,
    ISNULL(SUM(Highschool),0)  as Highschool,
    ISNULL(SUM(College),0)  as College,
    ISNULL(SUM(PWD),0)  as PWD, 
    ISNULL(SUM([AR Users]),0)  as ARUsers,
    ISNULL(SUM([12 Below]),0)  as age1,
    ISNULL(SUM([13-21]),0)  as age2,
    ISNULL(SUM([22-35]),0)  as age3,
    ISNULL(SUM([36-50]),0)  as age4,
    ISNULL(SUM([51-65]),0)  as age5,
    ISNULL(SUM([65 Above]),0)  as age6
FROM  [tbl_ForeignVisit] 
where [Date Added] >= @DateAdded"

SqlCommand cmd2 = new SqlCommand(sqlQuery);
cmd2.Parameters.Add("@DateAdded", SqlDbType.Date);
cmd2.Parameters["@DateAdded"].Value = dateTimePicker1.Value.Date;

笔记

如果要通过比较date类型获取数据。我会设置 SqlDbType.Date参数以确保date可以比较类型。

推荐阅读