sql - 如何根据多个过滤条件动态获取记录

Question

我有下表：

您能帮我如何动态获得以下场景的结果吗？

如果我提供 Emp = 1 和 HeaderID = 123 并且 Status = Failed，它应该返回结果如下：

 

如果我提供 Emp =1 和 HeaderID = 111 并且 status = Failed，它应该返回结果如下：

请提前谢谢。

标签： sqloracleoracle11g

Answer 1

我认为您还需要一列，例如时间或额外的 ID 或金额，以便在显示数据时进行排序。为了提供这个标准，我time在您的数据中添加了一个时间列（），如下面的演示所示：

with t(empid,headerid,status,time) as
(
 select 1,123,'Failed' , '2019-06-22 17:00:00' from dual union all
 select 1,321,'Pending', '2019-06-22 17:10:00' from dual union all
 select 1,555,'Pending', '2019-06-22 17:20:00' from dual union all
 select 1,111,'Failed' , '2019-06-22 17:30:00' from dual union all
 select 1,222,'Pending', '2019-06-22 17:40:00' from dual union all
 select 2,333,'Failed' , '2019-06-22 17:50:00' from dual union all
 select 2,444,'Pending', '2019-06-22 18:00:00' from dual union all
 select 3,555,'Failed' , '2019-06-22 18:10:00' from dual 
), t2 as  
(
select sum(case when status = 'Failed' then 1 else 0 end) 
       over (partition by empid order by time) as rn,
       t.*
  from t
) 
select *
  from t2
 where (rn, empid) in 
          ( select rn, empid 
              from t2 
             where empid = &i_empid -- 1 
               and headerid = &i_headerid -- 123
               and status = 'Failed' )
 order by time;

您可以根据需要分别替换1&123或1&111或2&333或3& 555。

Demo

编辑（由于您的评论）：

如果您想在具有状态的行Pending之后返回具有状态的第一行，Failed您可以尝试使用：

with t(empid,headerid,status,time) as
(
 select 1,123,'Failed' , '2019-06-22 17:00:00' from dual union all
 select 1,321,'Pending', '2019-06-22 17:10:00' from dual union all
 select 1,555,'Pending', '2019-06-22 17:20:00' from dual union all
 select 1,111,'Failed' , '2019-06-22 17:30:00' from dual union all
 select 1,222,'Pending', '2019-06-22 17:40:00' from dual union all
 select 2,333,'Failed' , '2019-06-22 17:50:00' from dual union all
 select 2,444,'Pending', '2019-06-22 18:00:00' from dual union all
 select 3,555,'Failed' , '2019-06-22 18:10:00' from dual 
), t2 as  
(
select sum(case when status = 'Failed' then 1 else 0 end) 
       over (partition by empid order by time) as rn,     
       t.*
  from t
), t3 as
(
select t2.*,
       row_number() over (partition by empid,rn,status order by time) as rn2      
  from t2
)
select *
  from t3
 where (rn,rn2, empid) in 
          ( select rn,rn2, empid 
              from t3 
             where empid = 1--&i_empid
               and headerid = 123 -- &i_headerid
               and status = 'Failed'
                )
   and status = 'Pending';

您可以再次替换上述查询中使用的相同值。