SQL Oracle:从同一行的不同列中选择最大值
声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow
原文地址: http://stackoverflow.com/questions/2928102/
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
Oracle : select maximum value from different columns of the same row
提问by gregseth
The whole question is pretty much in the title. For each row of the table I'd like to select the maximum of a subset of columns.
整个问题几乎都在标题中。对于表格的每一行,我想选择列子集的最大值。
For example, from this table
例如,从这张表
name m1 m2 m3 m4
A 1 2 3 4
B 6 3 4 5
C 1 5 2 1
the result would be
结果是
name max
A 4
B 6
C 5
The query must be compatible oracle 8i.
查询必须兼容oracle 8i。
回答by APC
Given this test data ...
鉴于此测试数据...
SQL> select *
2 from your_table
3 /
NAME M1 M2 M3 M4
---- ---------- ---------- ---------- ----------
A 1 2 3 4
B 6 3 4 5
C 1 5 2 1
SQL>
... a straightforward GREATEST() call will give the desired result:
...一个简单的 GREATEST() 调用将给出所需的结果:
SQL> select name
2 , greatest(m1, m2, m3, m4) as the greatest_m
3 from your_table
4 /
NAME THE_GREATEST_M
---- --------------
A 4
B 6
C 5
SQL>
Note that greatest()
will return NULL if any of the arguments are null. If this is a problem then use nvl()
to provide a default value which won't distort the outcome. For instance, if no values can be negative....
请注意,greatest()
如果任何参数为空,则将返回 NULL。如果这是一个问题,则使用nvl()
提供一个不会扭曲结果的默认值。例如,如果没有值可以是负数......
SQL> select name
2 , greatest(nvl(m1,0), nvl(m2,0), nvl(m3,0), nvl(m4,0)) as the greatest_m
3 from your_table
4 /
NAME THE_GREATEST_M
---- --------------
A 4
B 6
C 5
SQL>
回答by Matt
Use GREATEST
but also handle possible NULL
's
使用GREATEST
但也处理可能NULL
的
SELECT name, GREATEST(NVL(m1,0), NVL(m2,0), NVL(m3,0), NVL(m4,0)) AS "Max"
FROM yourtable
Input:
输入:
name m1 m2 m3 m4
A 1 2 3 4
B 6 3 4 5
C 1 5 2 1
Output:
输出:
NAME Max
A 4
B 6
C 5
SQL Fiddle: http://sqlfiddle.com/#!4/ae268/7/0
SQL小提琴:http://sqlfiddle.com/#!4/ae268/7/0
Input:
输入:
name m1 m2 m3 m4
A 1 2 3 null
B 6 null 4 5
C 1 5 2 1
Output:
输出:
NAME Max
A 3
B 6
C 5
SQL Fiddle: http://sqlfiddle.com/#!4/b1c46/1/0
SQL小提琴:http://sqlfiddle.com/#!4/b1c46/1/0