Oracle:从同一行的不同列中选择最大值 [英] Oracle : select maximum value from different columns of the same row

问题描述

整个问题几乎都在标题中.我想为表格的每一行选择最大的列子集.

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.

例如，在此表中

name m1 m2 m3 m4
A    1  2  3  4
B    6  3  4  5
C    1  5  2  1

结果将是

name max
A    4
B    6
C    5

查询必须与oracle 8i兼容.

The query must be compatible oracle 8i.

推荐答案

给出此测试数据...

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>

...一个直接的GREATEST()调用将给出所需的结果:

... a straightforward GREATEST() call will give the desired result:

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>

请注意，如果任何参数为null，则greatest()将返回NULL.如果这是一个问题，请使用nvl()提供一个默认值，该值不会使结果失真.例如，如果没有值可以为负....

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....

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>

这篇关于Oracle:从同一行的不同列中选择最大值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！