可以使用CASE表达式：

SELECT field_x,
       field_y,
       CASE
       WHEN watermelon = GREATEST(watermelon, orange, cabbage)
       THEN 'watermelon'
       WHEN orange = GREATEST(watermelon, orange, cabbage)
       THEN 'orange'
       WHEN cabbage = GREATEST(watermelon, orange, cabbage)
       THEN 'cabbage'
       END AS fruit
FROM   table_name

其中，对于示例数据：

CREATE TABLE table_name (field_x, field_y, watermelon, orange, cabbage) AS
SELECT 'lorem', 'ipsum', 4, 2, 5 FROM DUAL UNION ALL
SELECT 'dolor', 'sit',   9, 0, 7 FROM DUAL UNION ALL
SELECT 'amet',  'elit',  6, 9, 1 FROM DUAL;

输出：
| 字段_X|字段_Y|水果|
| - ------|- ------|- ------|
| 洛雷姆|伊普苏姆|卷心菜|
| 悲痛|坐|西瓜|
| 阿梅特|精英|橙|
如果可以有多个最大值，则：

SELECT field_x,
       field_y,
       SUBSTR(
         CASE
         WHEN watermelon = GREATEST(watermelon, orange, cabbage)
         THEN ', watermelon'
         END
         ||
         CASE
         WHEN orange = GREATEST(watermelon, orange, cabbage)
         THEN ', orange'
         END
         ||
         CASE
         WHEN cabbage = GREATEST(watermelon, orange, cabbage)
         THEN ', cabbage'
         END,
         3
       ) AS fruit
FROM   table_name

如果不想使用CASE表达式，可以先使用UNPIVOT，然后进行聚合（但效率会降低）：

SELECT field_x,
       field_y,
       MAX(fruit) KEEP (DENSE_RANK LAST ORDER BY value) AS fruit
FROM   table_name
UNPIVOT (
  value
  FOR fruit IN (
    watermelon AS 'watermelon',
    orange     AS 'orange',
    cabbage    AS 'cabbage'
  )
)
GROUP BY field_x, field_y

| 字段_X|字段_Y|水果|
| - ------|- ------|- ------|
| 阿梅特|精英|橙|
| 悲痛|坐|西瓜|
| 洛雷姆|伊普苏姆|卷心菜|
fiddle

一个选项是使用MODEL子句：

Select  FIELD_X, FIELD_Y, SubStr(FRUIT, InStr(FRUIT, '_') + 1) "FRUIT"
From    (   Select  ROWNUM "RN", FIELD_X, FIELD_Y, CAST('xxx' As VarChar2(12)) "FRUIT",
                    WATERMELON, WATERMELON || '_WATERMELON' "F_1", 
                    ORANGE, ORANGE || '_ORANGE' "F_2", 
                    CABBAGE, CABBAGE || '_CABBAGE' "F_3"
            From    tbl
        )   MODEL   Partition By (FIELD_X, FIELD_Y)
                    Dimension By (RN)
                    Measures  (FRUIT, F_1, F_2, F_3, WATERMELON, ORANGE, CABBAGE) 
            RULES   (  
                       FRUIT[ANY] = GREATEST( F_1[CV()], F_2[CV()], F_3[CV()]  )  
                    )

...其中包含您的示例数据：

WITH
    tbl AS
        (
            Select  'lorem' "FIELD_X", 'ipsum' "FIELD_Y", 4 "WATERMELON", 2 "ORANGE", 5 "CABBAGE" From Dual Union All
            Select  'dolor' "FIELD_X", 'sit'   "FIELD_Y", 9 "WATERMELON", 0 "ORANGE", 7 "CABBAGE" From Dual Union All
            Select  'amet'  "FIELD_X", 'elit'  "FIELD_Y", 6 "WATERMELON", 9 "ORANGE", 1 "CABBAGE" From Dual 
        )

...结果：

FIELD_X FIELD_Y FRUIT      
------- ------- ------------
lorem   ipsum   CABBAGE      
dolor   sit     WATERMELON   
amet    elit    ORANGE

......对于MTO评论中提到的情况----模型条款非常灵活，因此可以调整以处理10以上的值。在这种情况下，可以修改规则以检查数值：

RULES   (  
           FRUIT[ANY] = CASE GREATEST( WATERMELON[CV()], ORANGE[CV()], CABBAGE[CV()] ) 
                              WHEN WATERMELON[CV()] THEN F_1[CV()] 
                              WHEN ORANGE[CV()] THEN F_2[CV()]
                              WHEN CABBAGE[CV()] THEN F_3[CV()] 
                        END
        )

......最初的答案（如前所述）是针对问题中提供的样本数据。可能会有更多的问题，如如果两个水果在行中具有相同的最大值会怎样？可能会有更多的问题（如往常一样），但模型子句是非常可调的，快速和可靠的。

使用unpivot和row_number的替代解决方案

with frt as (
select 
  FIELD_X, FIELD_Y, FRUIT, VALUE,
  row_number() over (partition by FIELD_X, FIELD_Y order by value desc, FRUIT) rn
from tab
unpivot (
  value for fruit in (
    watermelon AS 'watermelon',
    orange     AS 'orange',
    cabbage    AS 'cabbage'
  ))  
)
select 
  FIELD_X, FIELD_Y, FRUIT
from frt
where rn = 1

如果您只想输出一个值，此解决方案允许控制连接的顺序和选择。参见row_number的order by子句

order by value desc, FRUIT   --< this will select the lowest fruit (string) from the ties.

此查询还适用于 fruit 列可为空的情况，此时使用Oracle greatest函数的解决方案将无法返回null。