row_number()降序查询时如何无需额外join插入min(created)统计字段

解决方案 #

你可以直接在原有窗口函数逻辑的基础上新增聚合类窗口函数计算同一机场的最早创建时间，无需额外CTE和JOIN操作，修改后的查询代码如下：

declare @Tickets table
(
    Airport varchar(10),
    TicketNum varchar(10),
    Created date,
    Modified date,
    LastModified date,
    Modified_By varchar(10),
    TicketStatus varchar(10), 
    AssignedTo varchar(10)
)
insert into @Tickets
select 'JFK', '001', '9/25/2021', '9/26/2021', '9/29/2021', 'Jimmy', 'Open', 'Ralph' union
select 'EWR', '001', '9/25/2021', '9/26/2021', '9/29/2021', 'Jimmy', 'Open', 'Ralph' union
select 'STI', '001', '9/25/2021', '9/26/2021', '9/29/2021', 'Jimmy', 'Open', 'Ralph' union
select 'JFK', '002', '9/28/2021', '9/28/2021', '9/30/2021', 'Mary', 'Open', 'Andrew'

select Airport, First_Created, lastmodified, assignedto, Modified_By
from
(
  select airport, 
    lastmodified, 
    assignedto, 
    Modified_By,
    row_number() over(partition by airport order by lastmodified desc) rn,
    min(Created) over(partition by airport) as First_Created
  from @Tickets
) src
where rn = 1

逻辑说明 #

这里使用的min(Created) over(partition by airport)是聚合类窗口函数，它会在每个机场的分组内计算所有工单的最早创建日期，同一个机场的所有行都会得到相同的First_Created值，后续筛选出rn=1（最新修改的工单行）的时候，自然就带上了对应机场的最早创建时间。

运行后得到的结果和你预期一致：

Airport   First_Created  lastmodified  assignedto   Modified_By
JFK       2021-09-25     2021-09-30    Andrew       Mary
EWR       2021-09-25     2021-09-29    Ralph        Jimmy
STI       2021-09-25     2021-09-29    Ralph        Jimmy

内容的提问来源于stack exchange，提问作者fdkgfosfskjdlsjdlkfsf