oracle SQL从具有空值的行数据中选择MIN值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12009817/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Selecting MIN value from row data with null values
提问by jero
My table in Oracle is like this
我在oracle的表是这样的
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
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.
我还可以在上列中设置 0 而不是 null。我需要从忽略 null 或 0 的 Col1、Col2、Col3 中找到最小值，并通过从 Col 中减去来填充 ColR。
EDIT: i wrote a CASE statement which doesn't work due to the null values inside my table.
编辑：我写了一个 CASE 语句，由于表中的空值而不起作用。
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
)
I have just included the case statement inside my select query. all the the column values all populated from another query.
我刚刚在我的选择查询中包含了 case 语句。所有列值都从另一个查询填充。
回答by Justin Cave
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>>
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 NULLor 0 values, then you'd probably want to add an additional check that if the result of that leastfunction is 999999999 that you return 0 or NULLor whatever else makes sense.
其中 999999999 是一些足够大的数值，它总是大于任何其他有效值。如果所有三列都可能有NULL或 0 值，那么您可能需要添加一个额外的检查，如果该least函数的结果是 999999999，您返回 0NULL或其他任何有意义的东西。
@X-Zero was kind enough to put together a working SQL Fiddle exampleof this construct. Note that his example is filtering out the rows where all three columns have either NULLor 0 values.
@X-Zero 非常友好地将这个构造的一个工作SQL Fiddle 示例放在一起。请注意，他的示例过滤掉了所有三列都为NULL0 值的行。
回答by Dhruvenkumar Shah
// IF YOU NEED MINIMAL FROM COL1 or COL (ANY COLUMN)
// 如果您需要来自 COL1 或 COL 的最小值（任何列）
SELECT MIN (COL1) FROM (SELECT * FROM TABLE WHERE COL1 IS NOT NULL)
SELECT MIN (COL1) FROM (SELECT * FROM TABLE WHERE COL1 IS NOT NULL)
Can you please elaborate I am not able to help you with this small set of info actually.
您能否详细说明我实际上无法帮助您处理这一小部分信息。
回答by mikeY
Oracle NVL Usage:
Oracle NVL 用法：
nvl(check_expression, replacement_value)
So nvl(col2,0) ought to take of nulls that mess with your math.
所以 nvl(col2,0) 应该采用与你的数学混乱的空值。
So try:
所以尝试：
CASE
WHEN nvl(col1,0) < nvl(col2,0) AND nvl(col1,0) < nvl(col3,0)
THEN Col - nvl(col1,0)
WHEN nvl(col2,0) < nvl(col1,0) AND nvl(col2,0) < nvl(col3,0)
THEN Col - nvl(col2,0)
ELSE Col - nvl(col3,0)
END ColR
EDIT: Taking X-Zero's point which I missed. I think if you replace the NULLS with 9999999 instead of 0, the logic will work, although that may be too specific to this sample data and not a real world solution.
编辑：考虑我错过的 X-Zero 点。我认为如果您用 9999999 而不是 0 替换 NULLS，逻辑将起作用，尽管这可能过于针对此示例数据而不是现实世界的解决方案。
回答by Cody
If you want to ignore nulls in a column, you could wrap them with the NVL function. This replaces null values in a column with the value specified, for which you could use some large number. For example:NVL(col1,99999)
如果要忽略列中的空值，可以使用 NVL 函数将它们包装起来。这将用指定的值替换列中的空值，您可以使用一些大数字。例如：NVL(col1,99999)
Oracle Database SQL Reference - NVL: http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm
Oracle 数据库 SQL 参考 - NVL：http: //docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm
