怎么输出最大值的那一字段名_Oracle管理

如何输出最大值的那一字段名

本帖最后由 kevinlynn 于 2015-03-16 16:59:18 编辑

有一张表，如下
NAME AAAA BBBB CCCC
TOM 1         2          3
JACK 3         4          6
jaccy 4         8          7
lina         9         3          1

输出
TOM       CCCC   3
JACK     CCCC   6
JACCY   BBBB    8
LINA       AAAA     9
------解决思路----------------------
WITH T AS
(SELECT 'TOM' AS NAME
        ,'1' AS AAAA
        ,'2' AS BBBB
        ,'3' AS CCCC
    FROM DUAL
  UNION ALL
  SELECT 'JACK'
        ,'3'
        ,'4'
        ,'6'
    FROM DUAL
  UNION ALL
  SELECT 'JACCY'
        ,'4'
        ,'8'
        ,'7'
    FROM DUAL
  UNION ALL
  SELECT 'LINA'
        ,'9'
        ,'3'
        ,'1'
    FROM DUAL)
SELECT NAME
      ,DECODE(GREATEST(AAAA, BBBB, CCCC), AAAA, 'AAAA', BBBB, 'BBBB', CCCC, 'CCCC')
      ,GREATEST(AAAA, BBBB, CCCC)
  FROM T

------解决思路----------------------

引用:

WITH T AS
(SELECT 'TOM' AS NAME
        ,'1' AS AAAA
        ,'2' AS BBBB
        ,'3' AS CCCC
    FROM DUAL
  UNION ALL
  SELECT 'JACK'
        ,'3'
        ,'4'
        ,'6'
    FROM DUAL
  UNION ALL
  SELECT 'JACCY'
        ,'4'
        ,'8'
        ,'7'
    FROM DUAL
  UNION ALL
  SELECT 'LINA'
        ,'9'
        ,'3'
        ,'1'
    FROM DUAL)
SELECT NAME
      ,DECODE(GREATEST(AAAA, BBBB, CCCC), AAAA, 'AAAA', BBBB, 'BBBB', CCCC, 'CCCC')
      ,GREATEST(AAAA, BBBB, CCCC)
  FROM T

------解决思路----------------------
如楼上所写，得知道或者说固定写好那些字段就行。赞楼上的。
不然的话得单独用数据字典+过程。
------解决思路----------------------

引用:

Quote: 引用:

WITH T AS
(SELECT 'TOM' AS NAME
        ,'1' AS AAAA
        ,'2' AS BBBB
        ,'3' AS CCCC
    FROM DUAL
  UNION ALL
  SELECT 'JACK'
        ,'3'
        ,'4'
        ,'6'
    FROM DUAL
  UNION ALL
  SELECT 'JACCY'
        ,'4'
        ,'8'
        ,'7'
    FROM DUAL
  UNION ALL
  SELECT 'LINA'
        ,'9'
        ,'3'
        ,'1'
    FROM DUAL)
SELECT NAME
      ,DECODE(GREATEST(AAAA, BBBB, CCCC), AAAA, 'AAAA', BBBB, 'BBBB', CCCC, 'CCCC')
      ,GREATEST(AAAA, BBBB, CCCC)
  FROM T
那个‘AAAA’ 能不能直接关联个系统表获取字段名

表字段的信息可以从USER_TAB_COLUMNS这里面取得
不过不推荐在业务处理里面访问数据字典这种做法
而且你看看表结果，也不一定能满足你的需求