如何在tsql中找到第n项

Question

我有一个如下表所需

Term
0
8
24
48
80
X

的输出是

Term1        Term2             Diff

0               8               8

8              24               16

24             48               24

48             80               32

80             x                40

我有下面的脚本

Declare @t Table(Term varchar(10))
Insert Into @t 
Select '0' Union All
Select '8' Union All
Select '24' Union All
Select '48' Union All
Select '80' Union All
Select 'X'

到目前为止我已经尝试过，因为

;With Cte1 As
(
    Select rn = ROW_NUMBER() Over(Order By (select 1)),* From @t
)
,cte2 as(
Select 
    Term1 = (Select term from Cte1 where rn=1)
    ,Term2 = (Select term from Cte1 where rn=2)
    ,Diff = Cast((Select term from Cte1 where rn=2) as int) - Cast((Select term from Cte1 where rn=1) as int)
)


Select * from cte2

我不知道在 cte2 的递归部分要做什么..

需要帮助

Answer 1

我不确定为什么 80 和 x 之间的差异应该是 40，但您可以通过调整 CTE 以返回最后一行所需的内容来处理这个问题。

;WITH Cte1 AS
(
  SELECT rn = ROW_NUMBER() OVER (ORDER BY (SELECT 1)), * FROM @t
)
SELECT
  Term1 = Cte1.Term,
  Term2 = (SELECT Term FROM Cte1 AS a_CTE where a_CTE.rn = Cte1.rn + 1),
  Diff = CAST((SELECT CASE Term WHEN 'X' THEN 120 ELSE Term END
               FROM Cte1 AS a_CTE
               WHERE a_CTE.rn = Cte1.rn + 1) AS int)
         - CAST(Cte1.Term AS int)
FROM Cte1
WHERE ISNUMERIC(Cte1.Term) = 1

Answer 2

我建议使用 PIVOT 而不是递归 CTE。参见： http://msdn.microsoft.com/en-us/library/ ms177410.aspx