USE tempdb
GO
IF OBJECT_ID('test') IS NOT NULL
DROP TABLE test;
GO
CREATE TABLE test
(
id int identity(1,1),
num int
);
GO
INSERT INTO test VALUES(0);
INSERT INTO test VALUES(0);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(0);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(0);
INSERT INTO test VALUES(0);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(1);
INSERT INTO test VALUES(0);
GO
----------------------------------------
select *,
(select max(id) from test where num=a.num and id<a.id) as t,
id-(select max(id) from test where num=a.num and id<a.id) as d
from test as a
----------------------------------------
/*
id num t d
----------- ----------- ----------- -----------
1 0 NULL NULL
2 0 1 1
3 1 NULL NULL
4 1 3 1
5 0 2 3
6 1 4 2
7 0 5 2
8 0 7 1
9 1 6 3
10 1 9 1
11 1 10 1
12 0 8 4
(12 行受影响)
*/
