SQLserver遇到0作为除数咋办

WITH A AS (

SELECT COUNT

( NAME ) AS TRADITIONSUM

FROM

ODBusiness.DBO.ONCONTRACT

WHERE

DATEDIFF( MONTH, [INPUTTIME], GETDATE( ) ) = 0

AND

PMCSUM NOT IN ('0')

B AS (

SELECT COUNT

( NAME ) AS TRADITIONSUM

FROM

ODBusiness.DBO.ONCONTRACT

WHERE

DATEDIFF( MONTH, [INPUTTIME], GETDATE( ) ) = 1

AND

PMCSUM NOT IN ('0')

) SELECT

CAST((A.TRADITIONSUM - B.TRADITIONSUM) AS FLOAT) /(B.TRADITIONSUM) FROM

用户K0035705 发布于 2020-12-7 13:07

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：3 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共5回答

最佳回答

AmyQLv6初级互助
发布于2020-12-7 13:12

WITH A AS (

SELECT COUNT

( NAME ) AS TRADITIONSUM

FROM

ODBusiness.DBO.ONCONTRACT

WHERE

DATEDIFF( MONTH, [INPUTTIME], GETDATE( ) ) = 0

AND

PMCSUM NOT IN ('0')

B AS (

SELECT COUNT

( NAME ) AS TRADITIONSUM

FROM

ODBusiness.DBO.ONCONTRACT

WHERE

DATEDIFF( MONTH, [INPUTTIME], GETDATE( ) ) = 1

AND

PMCSUM NOT IN ('0')

) SELECT case when B.TRADITIONSUM=0 then ' ' else

CAST((A.TRADITIONSUM - B.TRADITIONSUM) AS FLOAT) /(B.TRADITIONSUM) end FROM

Doctor_WeiLv7中级互助
发布于2020-12-7 13:08

case when ，数据未0时特殊处理

krystal033Lv7高级互助
发布于2020-12-7 13:09

用where排除掉0值不行吗

举报收起评论(1)

zsh331 NULLIF(B.TRADITIONSUM,0)，一劳永逸

2020-12-07 13:20

snrtuemcLv8专家互助
发布于2020-12-7 13:09（编辑于 2020-12-7 13:24）

	WITH A AS (
		SELECT COUNT
			( NAME ) AS TRADITIONSUM 
		FROM
			ODBusiness.DBO.ONCONTRACT 
		WHERE
			DATEDIFF( MONTH, [INPUTTIME], GETDATE( ) ) = 0 
		AND 
		  PMCSUM NOT IN ('0')
		),
		B AS (
		SELECT COUNT
			( NAME ) AS TRADITIONSUM 
		FROM
			ODBusiness.DBO.ONCONTRACT 
		WHERE
			DATEDIFF( MONTH, [INPUTTIME], GETDATE( ) ) = 1 
			AND 
		  PMCSUM NOT IN ('0')
		) SELECT
		case when B.TRADITIONSUM =0 then (A.TRADITIONSUM - B.TRADITIONSUM) AS FLOAT
		else CAST((A.TRADITIONSUM - B.TRADITIONSUM) AS FLOAT) /(B.TRADITIONSUM) end FROM
		A,
		B