Sql 查询集合中的第一行、下一行、最后一行

Question

我有两张表，我试图将它们多次连接在一起。父表称为作业，子表称为路由。一个作业可以有 1 个或多个路由。我需要我的输出包含每项作业的一条记录，并通过三个单独的联接连接到路由表。一个连接用于当前数据（序列中的第一个空日期值），一个连接用于下一个数据（紧随当前数据之后的序列），最后一个连接用于最后一个数据（定义为作业的最高序列号）。

下面是我整理的一个小示例，用于提供示例数据和所需的输出。它将问题转化为更简单的形式，实际上只显示路由，而不显示作业表。如果我能找到一种方法来更轻松地提取当前值、下一个值和最后一个值，我就可以从那里获取。

我已通过许多联接尝试此查询，但当不存在下一个路由时（我需要空值），它似乎会忽略结果。执行左外连接并不能解决这个问题。我不确定这是因为它是SQL Server 2000还是什么。

drop table #routing

create table #routing
(
routingId int not null primary key,
jobid int not null,
sequence int not null,
sentdate datetime
)

insert into #routing
select
1, 1, 1, '1/1/2009'
union
select
2, 1, 2, '1/2/2009'
union
select
3, 1, 3, '1/3/2009'
union
select
4, 1, 4, null
union
select
5, 1, 5, null
union
select
6, 2, 1, '1/1/2009'
union
select
7, 2, 2, '1/2/2009'
union
select
8, 2, 3, '1/3/2009'

select * from #routing


/*
Expected Result:

Two Records, one for each job

JobId,  CurrentRoutingId,   NextRoutingId,  LastRoutingId
1       4                   5               5
2       null                null            8

*/

Answer 1

我已通过许多联接尝试此查询，但当不存在下一个路由时（我需要空值），它似乎会忽略结果。执行左外连接并不能解决这个问题。

确保在 JOIN 子句中（而不是 WHERE 子句中）针对外部连接表放置任何过滤器。例如：

select curr.jobid, curr.routingid, prev.routingid as prev_routingid
from #routing curr
left join #routing prev 
  on curr.jobid = prev.jobid
 and curr.sequence = prev.sequence+1

而不是：

select curr.jobid, curr.routingid, prev.routingid as prev_routingid
from #routing curr
left join #routing prev 
  on curr.jobid = prev.jobid
where curr.sequence = prev.sequence+1

第二个版本相当于内连接。

Answer 2

将这些值作为列存储在作业表中不是很有意义吗？然后在（我假设）您的工作流程需要时更新它们？然后，您可以在上一步中执行 2 个内部联接 Jobs->Routes，在下一步中执行 Jobs->Routes。

Answer 3

这是一种解决方案

select r.jobid, min(rn.routingid) as nextroutingid, max(rl.routingid) as lastroutingid,
max(rn.routingid) as currentroutingid
from routing r
    left join routing rn on (rn.jobid = r.jobid) and (rn.sentdate is null)
    left join routing rl on (rl.jobid = r.jobid)
group by r.jobid