我的样本数据如下所示
drop
table if exists #temp
select
* into #temp
from
(
values
('id100', 'status1', 1),
('id100', 'status2', 2),
('id100', 'status1', 3),
('id100', 'status0', 4),
('id100', 'status2', 5),
('id100', 'status2', 6),
('id100', 'status1', 7),
('id100', 'status1', 8),
('id100', 'status2', 9),
('id101', 'status1', 10),
('id101', 'status2', 11)
) t(id, status, rowNum)
我需要TSQL for each id返回紧跟在rowNum之前的rowNum,其中Status=‘status1’.我希望返回这个代码的代码
id | status | rowNum | value |
---|---|---|---|
id100 | status1 | 1 | |
id100 | status2 | 2 | 1 |
id100 | status1 | 3 | |
id100 | status0 | 4 | |
id100 | status2 | 5 | 3 |
id100 | status2 | 6 | 3 |
id100 | status1 | 7 | |
id100 | status1 | 8 | |
id100 | status2 | 9 | 8 |
id101 | status1 | 10 | |
id101 | status2 | 11 | 10 |
我试过这个,但不起作用.
SELECT
t1.id,
t1.status,
t1.rowNum,
(
select
MIN(t2.rowNum)
from
#temp t2
where
t2.id = t1.id
and t2.rowNum < t1.rowNum
and t1.status = 'status2'
) as test
from
#temp t1