在一个集合中，如何查询第一行、下一行和最后一行？——SQL查询

Question

我有两个表，想要多次将它们连接在一起。父表名为Jobs，子表名为Routings. 一个Job可以拥有一个或多个Routings。我需要输出每个Job的一条记录，并与三个不同的Routings表连接。一个连接是当前数据（序列中第一个空日期值），一个是下一个（紧随当前的序列），最后一个是最后一个（被定义为该工作的最高顺序号）。

以下是我准备的一个小样本，提供了示例数据和所需的输出。它将问题简化了，只显示了Routings而不是Job表。如果我能找到更容易提取当前、下一个和最后值的方法，我就可以从那里开始。

我尝试通过多次连接来查询，但当没有下一个路由存在时，它似乎会省略结果（我需要空值）。进行左外连接并没有解决这个问题。我不确定这是因为它是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

*/

- Justin

---

第二行，你是不是想说 LastRoutingId = 3？ - Adriaan Stander

+1 你提供临时表脚本真是太好了。 - gbn

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

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

- Peter Radocchia

---

我知道它一定是更简单的事情！感谢您阅读我的解释并提出了一个很好的建议。这解决了我的问题。 - Justin

Answer 2

这是一个解决方案

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

- NotMe

---

...测试过吗？会得到非常不同的结果。 - gbn

糟糕。他在示例数据中误读了一列。已修复。 - NotMe

Answer 3

存储这些值作为您的作业表中的列，难道不是很有意义吗？然后只需在工作流程需要时（我猜测）更新它们。然后，您可以在上一步中对作业-路径进行两次内部连接，并在下一步中对作业-路径进行两次内部连接。

- andrewWinn

---

这可能会让生活变得更容易，但我一直尽可能避免存储冗余数据。而且对我来说，这感觉很冗余，因为我知道可以通过适当的Transact-SQL检索值。 - Justin

@Justin：在这种情况下，冗余数据应该更可取。当然你可以编写SQL，但涉及的查询比简单的选择要复杂得多。特别是如果这经常运行。在这种情况下，我主张性能胜过重复存储。 - NotMe

我认为如果您的表中有3列[previous_step，current_step，next_step]，并且它们都是指向路由表的外键，那么这不会是冗余的。您没有存储冗余数据，实际上这将是第三范式设计。此外，根据您提供的表，您已经在这样做了。我只是提倡一种不同的观点。 - andrewWinn