如何获取TSQL中第一列的条目结尾?

问题描述

我有这样一张桌子:

Sequence
2089697
2089738
2089838
2090368
2090400

我想要另一列

Sequence; EndSequence  
2089697; 2089737
2089738; 2089837
2089838; 2090367
2090368; 2090399
2090400; null

EndSequence 将是 Sequence 列的下一条记录的结尾.

The EndSequence will be the end of the next record of the Sequence column.

推荐答案

FOR SQL SERVER 2005/2008

FOR SQL SERVER 2005/2008

WITH records
AS
(
  SELECT  Sequence,
          ROW_NUMBER() OVER (ORDER BY Sequence ASC) rn
  FROM    TableName
)
SELECT  a.Sequence, b.Sequence - 1 EndSequence
FROM    records a
        LEFT JOIN records b
          ON a.rn+1 = b.rn

• SQLFiddle 演示

对于 SQL SERVER 2012

FOR SQL SERVER 2012

SELECT  SEQUENCE,
        LEAD(SEQUENCE) OVER (ORDER BY SEQUENCE) - 1 EndSequence
FROM    TableName

• SQLFiddle 演示

这篇关于如何获取TSQL中第一列的条目结尾?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持html5模板网！