获取指定列为null的第一组行

I have an Approval table where there is a requestor, verifier and approver. Sometimes there are multiple verifiers in a certain request no:

我有一个Approval表，其中有一个请求者，验证者和批准者。有时在某个请求中有多个验证者没有：

RequestNo | UserName | Action    | Seq | ActionDate
R001      | JohnD    | Requestor | 1   |  01/01/2017
R001      | SamS     | Verifier  | 2   |  NULL
R001      | TrishL   | Verifier  | 3   |  NULL
R001      | GeorgeP  | Verifier  | 4   |  NULL
R001      | JackF    | Approver  | 5   |  NULL

What I need is to get the first Action group (Requestor, Verifier, Approver) with null value in ActionDate. I need to display the UserNames and Action under the first Action group with null ActionDate. In this case, it should be SamS, TrishL and GeorgeP. Thanks guys!

我需要的是在ActionDate中获得第一个具有空值的Action组（Requestor，Verifier，Approver）。我需要在第一个具有null ActionDate的Action组下显示UserNames和Action。在这种情况下，它应该是SamS，TrishL和GeorgeP。多谢你们！

3 个解决方案

#1

A query like this should get you the info that you want:

像这样的查询应该可以获得您想要的信息：

Select UserName, Action
From ActionGroups
Where Action in (
  Select top 1 Action 
  from ActionGroups 
  Where ActionDate Is Null
  Order by Action
)

In this case, you are filtering by a subquery which returns the first Action (sorted alphabetically) that has a null ActionDate.

在这种情况下，您将通过子查询进行过滤，该子查询返回具有空ActionDate的第一个Action（按字母顺序排序）。

#2

This is a bit ugly but should get you what is needed. You first assign consecutive rows with the same action to a group. Then get the max and min date for each group. Then check if both the max and min dates are null and pick the first such group.

这有点难看，但应该得到你需要的东西。首先将具有相同操作的连续行分配给组。然后获取每个组的最大和最小日期。然后检查最大和最小日期是否都为空并选择第一个这样的组。

select requestno,username,action,seq,actiondate 
from (select t.*
      ,min(case when maxdate is null and mindate is null then grp end) over(partition by requestno) as first_grp 
      from (select t.*
            ,max(actiondate) over(partition by requestno,grp) as maxdate
            ,min(actiondate) over(partition by requestno,grp) as mindate
            from (select t.*,sum(col) over(partition by requestno order by seq) as grp
                  from (select t.*
                        ,case when lag(action) over(partition by requestno order by seq)=action then 0 else 1 end as col
                        from t
                       ) t
                  ) t
           )t 
      ) t 
where first_grp=grp

#3

Try using the below query.

declare @tbl table
(
ReqNO varchar(10),
Username varchar(10),
[Action] varchar(20),
Seq bit,
[Date] datetime null
)

insert into @tbl values ('R001','JohnD','Requestor',1,'01/01/2017')
insert into @tbl values ('R001','SamS','Verifier',2,null)
insert into @tbl values ('R001','TrishL','Verifier',3,null)
insert into @tbl values ('R001','GeorgeP','Verifier',4,null)
insert into @tbl values ('R001','JackF','Approver',5,null)

--select * from @tbl

select t.Username from @tbl t where t.Action in(
select d.Action from @tbl d  where d.Date is null 
--and ReqNO='R001' 
group by (d.Action) having COUNT(d.Action)>1)

#1