如果值小于零，则返回零 [英] Return zero if value less than zero

问题描述

如果一个值小于零而不使用 case 语句，是否可以返回零?

Is it possible to return zero if a value is less than zero without using a case statement?

例如Max(a, 0) <-- 'Max' 不存在.

e.g. Max(a, 0) <-- 'Max' doesn't exist.

我希望我的代码尽可能简洁.

I would like my code to be as succinct as possible.

推荐答案

仅供娱乐:

DECLARE @a INT = -3
SELECT COALESCE(NULLIF (ABS(@a), -@a), 0)

这篇文章只是暗示了一个方向，CASE 表达式是获得预期结果的更好选择.

This post just hints in the direction that a CASE expression is a much better option to get the expected result.

NULLIF 毕竟只是一个花哨的CASE.上面的示例查询(在执行计划中)扩展为:

NULLIF is, after all, just a fancy CASE. The example query above expands (in the execution plan) to:

CASE 
    WHEN 
        CASE 
            WHEN abs([@a])=( -[@a]) 
            THEN NULL 
            ELSE abs([@a]) 
        END IS NOT NULL 
    THEN 
        CASE 
            WHEN abs([@a])=( -[@a]) 
            THEN NULL 
            ELSE abs([@a]) 
        END 
    ELSE (0) 
END

一个合适的CASE表达式:

-- All versions
SELECT CASE WHEN @a > 0 THEN @a ELSE 0 END;

-- SQL Server 2012 or later
SELECT IIF(@a > 0, @a, 0);

这篇关于如果值小于零，则返回零的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！