select sum(isnull(totalmoney,0)) as amount from bd_rfp b where ISNULL(totalmoney,0)!=0 and totalmoney is not null and b.rfpstate='win' and b.ifsign='Y' and DATEDIFF(year,getrfpdate,GETDATE())=0如何让amount值为空时等于0，基于sql server

您可以使用COALESCE函数将amount值为空时等于，基于sql server的语法如下：

SELECT COALESCE(SUM(ISNULL(totalmoney,)),) AS amount
FROM bd_rfp b
WHERE ISNULL(totalmoney,)!=
AND totalmoney IS NOT NULL
AND b.rfpstate='win'
AND b.ifsign='Y'
AND DATEDIFF(year,getrfpdate,GETDATE())=

相关问题

select COALESCE(SUM(ISNULL(totalmoney,0)),0) AS amount from bd_rfp b where ISNULL(totalmoney,0)!=0 and b.rfpstate='win' and b.ifsign='Y' and DATEDIFF(year,getrfpdate,GETDATE())=0返回值是空值为什么

这个问题可能是关于编程的，我可以回答。这个查询语句的目的是计算在当前年份内，所有已经赢得招标并且已经签署合同的项目的总金额。但是，如果所有的项目都没有金额或者金额为，那么查询结果就会返回空值。可能是因为没有符合条件的项目或者数据输入有误。

SELECT COMPANY_SEGMENT, totalMoney,educationMoney,cityEducationMoney, (totalMoney+constructionMoney+educationMoney+cityEducationMoney) FROM (SELECT COMPANY_SEGMENT, sum( abs( PERIOD_OCCURRENCE ) ) AS "totalMoney", sum( abs( PERIOD_OCCURRENCE ) ) * 0.07 AS "constructionMoney", sum( abs( PERIOD_OCCURRENCE ) ) * 0.03 AS "educationMoney", sum( abs( PERIOD_OCCURRENCE ) ) * 0.02 AS "cityEducationMoney" FROM INTELLIGENT_BALANCE_FORWARD WHERE IS_FLAG = 0 AND ACCOUNT_SEGMENT = '2104080100' GROUP BY COMPANY_SEGMENT) GROUP BY COMPANY_SEGMENT

您的查询语句如下：

SELECT COMPANY_SEGMENT, totalMoney, educationMoney, cityEducationMoney, (totalMoney+constructionMoney+educationMoney+cityEducationMoney)
FROM (
    SELECT
        COMPANY_SEGMENT,
        SUM(ABS(PERIOD_OCCURRENCE)) AS totalMoney,
        SUM(ABS(PERIOD_OCCURRENCE)) * 0.07 AS constructionMoney,
        SUM(ABS(PERIOD_OCCURRENCE)) * 0.03 AS educationMoney,
        SUM(ABS(PERIOD_OCCURRENCE)) * 0.02 AS cityEducationMoney
    FROM
        INTELLIGENT_BALANCE_FORWARD
    WHERE
        IS_FLAG = 0
        AND ACCOUNT_SEGMENT = '2104080100'
    GROUP BY
        COMPANY_SEGMENT
) 
GROUP BY COMPANY_SEGMENT;

这个查询语句从表`INTELLIGENT_BALANCE_FORWARD`中选择了满足条件`IS_FLAG = 0`和`ACCOUNT_SEGMENT = '2104080100'`的记录。然后，根据`COMPANY_SEGMENT`对结果进行分组，并计算了每个分组的总金额(`totalMoney`)、建设金额(`constructionMoney`)、教育金额(`educationMoney`)、城市教育金额(`cityEducationMoney`)和总金额(`totalMoney+constructionMoney+educationMoney+cityEducationMoney`)。最后，按照`COMPANY_SEGMENT`进行分组并返回结果。