有这么一个表,其中一列是
X
2502000001
2502000080
2502000081
2502000090
2502000091
2502000122
2502000123
2502000127
2502000128
2502000164
如何求后一行减前一行再加1的值?比如2502000080-2502000001+1=80,2502000090-2502000081+1=10,2502000122-2502000091=32等等,谢谢
X
2502000001
2502000080
2502000081
2502000090
2502000091
2502000122
2502000123
2502000127
2502000128
2502000164
如何求后一行减前一行再加1的值?比如2502000080-2502000001+1=80,2502000090-2502000081+1=10,2502000122-2502000091=32等等,谢谢
create table #test
([x] numeric,
[id] int identity,
[result] numeric )
insert into #test (x)
select 2502000001 union all
select 2502000080 union all
select 2502000081 union all
select 2502000090 union all
select 2502000091 union all
select 2502000122 union all
select 2502000123 union all
select 2502000127 union all
select 2502000128 union all
select 2502000164update #test set result = c.result
from (select (a.x - b.x) + 1 as result,b.[id] from #test b inner join (select * from #test) a on a.[id] = b.[id]+1) c
where #test.[id] = c.[id]select * from #test
-----------------result----------------------
x id result
2502000001 1 80
2502000080 2 2
2502000081 3 10
2502000090 4 2
2502000091 5 32
2502000122 6 2
2502000123 7 5
2502000127 8 2
2502000128 9 37
2502000164 10 null
就能出来了`
(
IX BIGINT NOT NULL
)
INSERT INTO T1
SELECT 2502000001 UNION ALL
SELECT 2502000080 UNION ALL
SELECT 2502000081 UNION ALL
SELECT 2502000090 UNION ALL
SELECT 2502000091 UNION ALL
SELECT 2502000122 UNION ALL
SELECT 2502000123 UNION ALL
SELECT 2502000127 UNION ALL
SELECT 2502000128 UNION ALL
SELECT 2502000164SELECT ROW_NUMBER() OVER(ORDER BY IX) AS IID,IX
INTO #T1
FROM T1DECLARE @A INT,
@B INT,
@C INT,
@D BIGINT,
@E BIGINT,
@F BIGINT
SET @B=2
SET @C=1
SELECT @A=COUNT(*) FROM #T1WHILE @B<=@A
BEGIN
SELECT @D=IX FROM #T1 WHERE IID=@B
SELECT @E=IX FROM #T1 WHERE IID=@C
SET @F=@D-@E+1
PRINT @F
SET @B=@B+2
SET @C=@C+2
END
create table #tmp
(
id int identity(1,1),
x1 int
)--drop table #tmp
insert into #tmp
select 132
union all
select 153
union all
select 254
union all
select 257
union all
select 654
union all
select 655select * from #tmpselect
a.*,
result=(select top 1 b.x1-a.x1+1 from #tmp b where b.id>a.id )
from #tmp a--也可使用row_number() over() 构造ID列.再进行比较
(
X nvarchar(50)
)
insert into #tx
select '2502000001' union all
select '2502000080' union all
select '2502000081' union all
select '2502000090' union all
select '2502000091' union all
select '2502000122' union all
select '2502000123' union all
select '2502000127' union all
select '2502000128' union all
select '2502000164'select cast((select top 1 x from #tx where x>a.x order by x ) as numeric)-cast(x as numeric) from #tx a order by x