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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-01 06:23:00  来源:igfitidea点击:

Oracle : select maximum value from different columns of the same row

sqloracleoracle8i

提问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 GREATESTbut 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