将PostgreSQL取最大值对应列名的查询适配为Oracle兼容语句

现有表employment_by_industry，需求为：为表中每一行选取值最大的列名，且SQL查询中列名仅出现一次。以下是PostgreSQL实现该需求的语句：

select objectid, (SELECT industry FROM (values 
       (agr_forest_fish, 'agr_forest_fish'),
       (mining_quarry, 'mining_quarry'),
       (mfg, 'mfg'),
       (electric, 'electric'),
       (water_sew, 'water_sew')
  ) as t(emp, industry)
  order by emp DESC
  LIMIT 1)
from employment_by_industry

请问该PostgreSQL语句能否适配到任意版本的Oracle中？

不能直接适配所有Oracle版本，具体差异和替代方案如下：

• 核心语法不兼容点：PostgreSQL用VALUES子句构造行集的写法，在Oracle 12c之前不支持直接在子查询中使用；另外Oracle没有LIMIT关键字，需用ROWNUM或FETCH FIRST 1 ROW ONLY限制返回行数。
• 分版本适配写法：
  1. Oracle 12c及以上版本：可兼容VALUES子句，替换LIMIT为FETCH FIRST即可，改写后语句：

     SELECT objectid,
            (SELECT industry
             FROM (VALUES (agr_forest_fish, 'agr_forest_fish'),
                          (mining_quarry, 'mining_quarry'),
                          (mfg, 'mfg'),
                          (electric, 'electric'),
                          (water_sew, 'water_sew')) t(emp, industry)
             ORDER BY emp DESC
             FETCH FIRST 1 ROW ONLY) AS max_industry
     FROM employment_by_industry;
     

  2. Oracle 11g及更早版本：不支持直接用VALUES构造行集，需用UNION ALL模拟行数据，同时用ROWNUM限制行数，改写后语句：

     SELECT objectid,
            (SELECT industry
             FROM (SELECT agr_forest_fish AS emp, 'agr_forest_fish' AS industry FROM dual
                   UNION ALL
                   SELECT mining_quarry, 'mining_quarry' FROM dual
                   UNION ALL
                   SELECT mfg, 'mfg' FROM dual
                   UNION ALL
                   SELECT electric, 'electric' FROM dual
                   UNION ALL
                   SELECT water_sew, 'water_sew' FROM dual) t
             ORDER BY emp DESC
             WHERE ROWNUM = 1) AS max_industry
     FROM employment_by_industry;
     

• 特殊场景说明：如果存在多个列值同为最大值的情况，上述写法会随机返回其中一个列名；若需结果确定，可在ORDER BY后追加industry字段排序。

内容的提问来源于stack exchange，提问作者User1974