Oracle decode+date+single行

Question

我使用的是oracle，下面是详细信息

SQL> with t as (select 12345 as ID,1 as TASK_ID,-1 as ASSIGNED_ID,null as ASSIGNED_GRP,sysdate-12/24 as ASSIGNED_DATE from dual union all
select 12345,2,67890,null,sysdate-11/24 from dual union all
select 12345,2,78901,null,sysdate-10/24 from dual union all
select 12345,2,-1,1111,sysdate-09/24 from dual union all
select 12345,2,67890,null,sysdate-08/24 from dual union all
select 12345,2,-1,2222,sysdate-07/24 from dual union all
select 12345,2,78901,null,sysdate-06/24 from dual union all
select 12346,2,67890,null,sysdate-05/24 from dual union all
select 12346,2,-1,1111,sysdate-04/24 from dual union all
select 12346,2,67890,null,sysdate-03/24 from dual union all
select 12346,2,78901,null,sysdate-02/24 from dual)
select ID,decode(ASSIGNED_GRP, NULL, decode(ASSIGNED_ID, NULL, '*', '-1', '*', ASSIGNED_ID), ASSIGNED_GRP),ASSIGNED_DATE from t;  

      ID DECODE(ASSIGNED_GRP,NULL,DECODE(ASSIGNED ASSIGNED_DATE
-------- ---------------------------------------- -----------------
   12345 *                                        25-APR-2014 11:33
   12345 67890                                    25-APR-2014 12:33
   12345 78901                                    25-APR-2014 13:33
   12345 1111                                     25-APR-2014 14:33
   12345 67890                                    25-APR-2014 15:33
   12345 2222                                     25-APR-2014 16:33
   12345 78901                                    25-APR-2014 17:33
   12346 67890                                    25-APR-2014 18:33
   12346 1111                                     25-APR-2014 19:33
   12346 67890                                    25-APR-2014 20:33
   12346 78901                                    25-APR-2014 21:33

我正在寻找如下所示的预期输出

ID    ASSIGNED Hours
-----------------
12345 67890    2
12345 78901    2
12345 1111     1
12345 2222     1
12346 67890    2
12346 78901    2
12345 1111     1

我尝试过使用时间延迟参数来做这件事，但是遇到了解码功能的问题。你能建议一下吗？

Answer 1

您似乎在计算符合条件的行数。

以下代码将生成所需的结果集，尽管顺序不同：

select ID, 
       decode(ASSIGNED_GRP, NULL, decode(ASSIGNED_ID, NULL, '*', '-1', '*', ASSIGNED_ID), ASSIGNED_GRP),
       count(*)
from t
where assigned_grp is not null or (assigned_id is not null and assigned_id <> -1)
group by ID,
       decode(ASSIGNED_GRP, NULL, decode(ASSIGNED_ID, NULL, '*', '-1', '*', ASSIGNED_ID), ASSIGNED_GRP)   
order by 1, 2  ;    

sqlfiddle.com