Oracle SQL:根据哪个列具有最高值返回字符串

Question

呈现

给我的表格看起来与此类似

CREATE TABLE user_status (
  user_id NUMBER(10,0) PRIMARY KEY,  
  applied TIMESTAMP,
  joined TIMESTAMP,
  last_attended TIMESTAMP,
  quit TIMESTAMP
);

数据库是Oracle 11g。

如果我想根据应用、已加入、

last_attended或退出返回已应用、已加入、活动或非活动，SQL 查询可以使用什么是最新的和相应的日期。

如果我可以获得具有最新日期的列的名称(而不是已应用、已加入、活动或非活动(，这也是可以接受的，如果这大大简化了查询。

示例行：

1 | 28-JUL-15 03.37.07 PM | 29-JUL-15 03.37.07 PM | 30-JUL-15 03.37.07 PM | (null)
2 | 18-JUL-15 03.55.24 PM | 19-JUL-15 03.55.24 PM | 20-JUL-15 03.55.24 PM | 31-JUL-15 03.55.24 PM

预期成果：

1 | ACTIVE | 30-JUL-15 03.37.07 PM
2 | INACTIVE | 31-JUL-15 03.55.24 PM

更新

以下是我基于Gordon Linoff的答案的解决方案：

select (case greatest(coalesce(applied, to_date('0001-01-01', 'YYYY-MM-DD')), 
                      coalesce(joined, to_date('0001-01-01', 'YYYY-MM-DD')),
                      coalesce(last_attended, to_date('0001-01-01', 'YYYY-MM-DD')),
                      coalesce(quit, to_date('0001-01-01', 'YYYY-MM-DD')))
             when applied 
             then 'APPLIED'
             when joined
             then 'JOINED'
             when last_attended
             then 'INACTIVE'
             when quit
             then 'QUIT'
             else null
        end) as status, greatest(coalesce(applied, to_date('0001-01-01', 'YYYY-MM-DD')), 
                      coalesce(joined, to_date('0001-01-01', 'YYYY-MM-DD')),
                      coalesce(last_attended, to_date('0001-01-01', 'YYYY-MM-DD')),
                      coalesce(quit, to_date('0001-01-01', 'YYYY-MM-DD'))) as status_date
from user_status;

Answer 1

如果所有值都有值，您可以执行以下操作：

select us.*
       (case when applied = greatest(applied, joined, last_attended, quit)
             then 'Applied'
             when joined = greatest(applied, joined, last_attended, quit)
             then 'Joined'
             when last_attended = greatest(applied, joined, last_attended, quit)
             then 'Last Attended'
             else 'Quit'
        end)
from user_status us

如果这些可以有NULL值，那么你需要更复杂的逻辑，因为如果有任何参数NULL greatest()返回NULL。 假设所有这些都是过去，那么像这样：

select (case greatest(coalesce(applied, date '0000-01-01'), 
                      coalesce(joined, date '0000-01-01'),
                      coalesce(last_attended, date '0000-01-01'),
                      coalesce(quit, date '0000-01-01') )
             when applied 
             then 'Applied'
             when joined
             then 'Joined'
             when last_attended
             then 'Last Attended'
             else 'Quit'
        end)
from . . .

Answer 2

select user_id, case greatest(applied, joined, last_attended, quit)
    when applied then 'APPLIED'
    when joined then 'JOINED'
    when last_attended then 'ACTIVE'
    when quit then 'INACTIVE' end
from user_status

如果字段之间平局，则case表达式中的第一个字段获胜。

Answer 3

这有帮助吗？

SELECT 'APPLIED', MAX(applied) as val from user_status
  UNION ALL
SELECT 'JOINED', MAX(joined) as val  from user_status
  UNION ALL
SELECT 'ACTIVE', MAX(last_attended) as val  from user_status
  UNION ALL
SELECT 'INACTIVE', MAX(quit) as val from user_status

例如

您可能希望将其限制为特定用户 最里面的查询...

SELECT what 
FROM (
  SELECT what 
  FROM ( 
    SELECT 'APPLIED' as what, MAX(applied) as val from user_status
    UNION ALL
    SELECT 'JOINED' as what, MAX(joined) as val  from user_status
    UNION ALL
    SELECT 'ACTIVE' as what, MAX(last_attended) as val  from user_status
    UNION ALL
    SELECT 'INACTIVE' as what, MAX(quit) as val from user_status
  ) sub
  ORDER BY val DESC  
) sub2
WHERE rownum = 1

Answer 4

我做的与其他人略有不同..这种方法的优点是我也用它来检索数据(如果需要(.. ;)

  insert into user_status values ( 123, sysdate-10, sysdate-9, sysdate-8, sysdate-7 );
  insert into user_status values ( 234, sysdate-6, sysdate-1, sysdate-11, sysdate-16 );
  insert into user_status values ( 345, sysdate-5, sysdate-2, sysdate-12, sysdate-15 );
  insert into user_status values ( 456, sysdate-4, sysdate-3, sysdate-13, sysdate-14 );
  commit;
  with w_union as ( 
        select applied  dt, 'APPLIED' col, user_id
          from user_status
         union all
        select joined   dt, 'JOINED' col, user_id
          from user_status
         union all
        select last_attended  dt, 'LAST_ATTENDED' col, user_id
          from user_status
         union all
        select quit  dt, 'QUIT' col, user_id
          from user_status
     ),
     w_sub as (
        select  dt, col, user_id,
                row_number() over (order by dt desc) rnum
          from w_union
        )
  select dt, col, user_id
    from w_sub
   where rnum = 1
  /

  DT                             COL              USER_ID
  ------------------------------ ------------- ----------
  30-JUL-15 01.33.12.000000 PM   JOINED               234