--> 生成测试数据: @T DECLARE @T TABLE (a INT,b VARCHAR(3),c INT,d INT) INSERT INTO @T SELECT 1,'000',2,0 UNION ALL SELECT 1,'000',8,1 UNION ALL SELECT 1,'000',9,0 UNION ALL SELECT 1,'000',5,1--SQL查询如下:;WITH Liang AS ( SELECT *,rowid=ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM @T ) SELECT flag = (SELECT TOP(1) c FROM Liang WHERE rowid<T.rowid AND d=0 ORDER BY rowid DESC) FROM Liang AS T WHERE d=1;
;with t as (select *,rid = row_number() over (order by getdate()) from tb) , t1 as ( select a.*,b.rid as t from t as a left join t as b on a.rid < b.rid where b.d = 1 and a.d = 0) select a,b,c,d from t1 c where not exists(select 1 from t1 where t = c.t and c >c.c)
a.c
from
# a,
# b
where
a.tid=b.tid-1
and
b.d=1
-- Author: liangCK 小梁
-- Date : 2009-05-13 16:01:26
---------------------------------
--> 生成测试数据: @T
DECLARE @T TABLE (a INT,b VARCHAR(3),c INT,d INT)
INSERT INTO @T
SELECT 1,'000',2,0 UNION ALL
SELECT 1,'000',8,1 UNION ALL
SELECT 1,'000',9,0 UNION ALL
SELECT 1,'000',5,1--SQL查询如下:;WITH Liang AS
(
SELECT *,rowid=ROW_NUMBER() OVER(ORDER BY GETDATE())
FROM @T
)
SELECT
flag = (SELECT TOP(1) c FROM Liang
WHERE rowid<T.rowid AND d=0
ORDER BY rowid DESC)
FROM Liang AS T
WHERE d=1;
;with t
as
(select *,rid = row_number() over (order by getdate()) from tb)
, t1 as
(
select a.*,b.rid as t
from t as a
left join t as b on a.rid < b.rid
where b.d = 1 and a.d = 0)
select a,b,c,d
from t1 c
where not exists(select 1 from t1 where t = c.t and c >c.c)