SQL从具有空值的行数据中选择MIN值 [英] SQL Selecting MIN value from row data with null values

问题描述

我在Oracle中的表就是这样

My table in Oracle is like this

Col   Col1  Col2  Col3  ColR
--   ----  ----   ----  ---- 
30    73    40    null  -10
60    32    null  20     40
90    80    null  null   10
80    45    81    30     50

我也可以在上面的列中设置0而不是null. 我需要从Col1，Col2，Col3中找到最小值，忽略null或0，并通过从Col中减去来填充ColR.

I can also set 0 instead of null in the above column. I need to find the min value from Col1,Col2,Col3 ignoring null or 0 and populate the ColR by subtracting from Col.

我写了一个CASE语句，该语句由于表中的空值而无法正常工作.

i wrote a CASE statement which doesn't work due to the null values inside my table.

 SELECT col,col1,col2,col3,
  CASE
    WHEN Col1 < Col2 AND Col1  < Col3
    THEN Col - Col1
    WHEN Col2 < Col1 AND Col2  < Col3
    THEN Col - Col2
    ELSE Col - Col3
  END ColR
FROM
 (SELECT col,col1,
   CASE
    WHEN col22 IS NULL
    THEN NULL( i can also SET TO 0 but it will mess  WITH my other calculation TO find MIN)
      ELSE ROUND( 100* ( (col22) / (col44)))
    END col2 ,
    CASE
      WHEN col33 IS NULL
      THEN NULL
      ELSE ROUND( 100* ( (col33) / (col44)))
    END col3
  FROM TABLE
  )

我刚刚在我的选择查询中添加了case语句.所有的列值都从另一个查询填充.

I have just included the case statement inside my select query. all the the column values all populated from another query.

推荐答案

听起来您想要类似的东西

It sounds like you want something like

SELECT least( (case when col1 is null or col1 = 0 then 999999999 else col1 end),
              (case when col2 is null or col2 = 0 then 999999999 else col2 end),
              (case when col3 is null or col3 = 0 then 999999999 else col3 end) )
  FROM <<table name>>

其中999999999是某个数字值，该数字值足够大，使其始终大于任何其他有效值.如果所有三列都可能具有NULL或0值，那么您可能希望添加其他检查，以确保如果该least函数的结果为999999999，则返回0或NULL或任何其他值否则是有道理的.

where 999999999 is some numeric value that is large enough that it will always be larger than any other valid value. If it is possible that all three columns will have NULL or 0 values, then you'd probably want to add an additional check that if the result of that least function is 999999999 that you return 0 or NULL or whatever else makes sense.

@ X-Zero非常友好，可以整理一个有效的 SQL Fiddle示例此结构.请注意，他的示例正在过滤出所有三列均具有NULL或0值的行.

@X-Zero was kind enough to put together a working SQL Fiddle example of this construct. Note that his example is filtering out the rows where all three columns have either NULL or 0 values.

这篇关于SQL从具有空值的行数据中选择MIN值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！