我需要Oracle SQL语句方面的帮助。我需要以各种方式比较3列,并返回一个数字。
示例:列A为空&B列为Null,然后返回C列列B为空&列C为Null,然后返回列A列A为空&C列为空,然后返回B列列A是<B列,然后A列
等等。
我尝试过CASE/WHEN/ELSE和DECODE(Trim.LEAST在比较这3列时工作正常,只要它们不是NULL或我得到0。
我发现COALESCE(NULLIF语句非常有效,除了当我有多个值超过0的列时,它总是返回第一个值。我还可以在NULLIF函数的内部(或末尾)添加一个"Least"函数吗?这样,如果其中2列不为NULL,则比较这2列,并给我最小的值。
所需结果:
desc hrs cyc days interval hrs/cyc/days
============================================================
Task 1 72 0 0 72 hrs
Task 2 2400 1200 2190 2400 hrs
Task 3 600 0 180 600 hrs
Task 4 0 500 120 500 cyc
Task 5 0 0 30 30 day
在商业智能/Web Intelligence中获得上述结果的编码如下:
= If ( [Remain Hours]=0 And [Remain Cycles] = 0) Then [Schedule Days]
Else If ([Remain Cycles]=0 And [Remain Days]= 0) Then [Schedule Hours]
Else If ([Remain Hours] = 0 And [Remain Days] = 0 ) Then [Schedule Cycles]
Else If ([Remain Hours] = 0 And ([Remain Days] < [Remain Cycles]/6 ) ) Then [Schedule Days]
Else If ([Remain Hours] = 0 And ( [Remain Cycles]/6 < [Remain Days] ) ) Then [Schedule Cycles]
Else If ([Remain Hours] = 0 And ( [Remain Cycles]/6 > [Remain Days] ) ) Then [Schedule Days]
Else If ([Remain Cycles] = 0 And ([Remain Hours]/13 < [Remain Days] ) ) Then [Schedule Hours]
Else If ([Remain Cycles] = 0 And ([Remain Hours]/13 > [Remain Days] ) ) Then [Schedule Days]
Else If ([Remain Days] = 0 And ( [Remain Hours]/13 < [Remain Cycles]/6 ) ) Then [Schedule Hours]
Else If ([Remain Days] = 0 And ( [Remain Hours]/13 > [Remain Cycles]/6 ) ) Then [Schedule Cycles]
Else If ( ([Remain Hours]/13) < ([Remain Cycles]/6) And ( [Remain Hours]/13 < [Remain Days]) ) Then [Schedule Hours]
Else If ( ([Remain Cycles]/6 < [Remain Days]) And ( [Remain Cycles]/6 < [Remain Hours]/13) ) Then [Schedule Cycles]
Else If ( ( [Remain Days] < [Remain Cycles]/6 ) And ( [Remain Days] < [Remain Hours]/13) ) Then [Schedule Days]
据我所知,您希望从所有3列中选择最低值,但其中一些列可能为null,并且列中值的顺序不一定是升序。
假设至少1列具有非null值,这将适用于您:
SELECT LEAST(COALESCE(COL_A, 2147483647), COALESCE(COL_B, 2147483647), COALESCE(COL_C, 2147483647))
FROM YOUR_TABLE
2147483647
是PLS_INTEGER的最大可能值。假设所有的实际值都远小于这个最大值,并且LEAST
函数将返回任何列中的最低值。
如果所有三个列都可以为null,则会出现问题,因为这将返回结果的2147483647
。在这种情况下,您可以执行DECODE
来忽略该值,并返回NULL
:
SELECT DECODE(LEAST(COALESCE(COL_A, 2147483647), COALESCE(COL_B, 2147483647), COALESCE(COL_C, 2147483647)), 2147483647, NULL)
FROM YOUR_TABLE