时间戳

首页 > 解决方案 > Replace Null value to infinite

mysql - Replace Null value to infinite

问题描述

I have a table with field min, max and amount. The last value of the column max is always null and it denotes min value and above as per my logic. During BETWEEN query in sql I tried as:

 Select t.amount from my_table t where 25000 between t.min and NVL(t.max, ~0);

Also, I tried:

 Select t.amount  from my_table t where 25000 between t.min and COALESCE(t.max, ~0);

None of them return me a row if the value lies between min and NULL.

The sample Data is : enter image description here

标签： mysqldatabaseselectwhere-clausebetween

解决方案

How about:

25000 between t.min and nvl(t.max, 25000)

If t.max is null, the nvl() function returns 25000, which will satisfy the higher bound of the between operator.

If the value to compare is stored in a column, say col1, then you can do: 

col1 between t.min and nvl(t.max, col1)

推荐阅读