一个月一个月

如果yourvalue小于0，则0将作为较大值返回：

select  *,
        greatest(0,first_created-coalesce(submitted_time::date)) as create_duration,
        greatest(0,last_paid-coalesce(submitted_time::date)) as paid_duration
from my_table

这也会将null值更改为0。

case语句

如果你想保留null的结果，你可以求助于常规的case语句，为了给你的计算取别名，你必须把它放在一个子查询或cte中：

select *,
        case when create_duration<0 then 0 else create_duration end as create_duration_0,
        case when paid_duration<0 then 0 else paid_duration end as paid_duration_0
from (
    select  *,
            first_created-coalesce(submitted_time::date) as create_duration,
            last_paid-coalesce(submitted_time::date) as paid_duration
    from my_table ) as subquery;

一米九一

如果你把一个数和它的绝对值相加，然后除以2（求平均值），如果它是正数，你会得到相同的数，如果它是负数，你会得到零，因为负数总是会用它的绝对值来平衡自己：

• (-1+abs(-1))/2 = (-1+1)/2 = 0/2 = 0
• x1米11米1x

select *,
        (create_duration + abs(create_duration)) / 2 as create_duration_0,
        (paid_duration   + abs(paid_duration)  ) / 2 as paid_duration_0
from (
    select  *,
            first_created-coalesce(submitted_time::date) as create_duration,
            last_paid-coalesce(submitted_time::date) as paid_duration
    from my_table ) as subquery;

根据this demo，它比case稍快，大约和greatest()一样快，而不影响null的值。
请注意，select *从下面提取所有内容，因此您最终会看到create_duration和create_duration_0-您可以通过在外部查询中显式列出所需的输出列来消除它。您也可以不使用子查询/cte来重写它，重复计算，这看起来很难看，但在大多数情况下，planner会注意到重复，并只计算一次

select  *,
        case when first_created-coalesce(submitted_time::date) < 0
            then 0
            else first_created-coalesce(submitted_time::date)
        end as create_duration,
        (abs(last_paid-coalesce(submitted_time::date))+last_paid-coalesce(submitted_time::date))/2 as paid_duration
from my_table ) as subquery;

或使用标量子查询

select  *,
        (select case when a<0 then 0 else a end 
         from (select first_created-coalesce(submitted_time::date)) as alias(a) ) 
            as create_duration,
        (select case when a<0 then 0 else a end 
         from (select last_paid-coalesce(submitted_time::date)) as alias(a) ) 
            as paid_duration
from my_table ) as subquery;

在这种情况下，这两个都没有帮助，但很高兴知道。

如果您计划将SQL数据库附加到ASP.NET应用程序，则可以创建一个c#脚本来查询数据库，并使用以下命令：

Parameters.AddWithValue(‘Data You want to change’ ‘0’);

但是，如果您没有将SQL数据库与ASP.NET应用程序一起使用，这将不起作用。