求一SQL語句.

[csdyyr]

表t:
no flag
1 Y
2 N
3 N
4 Y
5 N
6 Y
...
如何得到下列結果(從flag為'Y'到下一條為'Y'之前的紀錄分組):
1 1
2 1
3 1
4 2
5 2
6 3
...

[子陌红尘]

update a
set
flag=(select count(1) from t where no<=a.no and flag='Y')
from
t a

[dawugui]

参阅这个.

表A记录：

1 开门
2 开门
3 关门
4 开门
5 开门
6 关门

请教是否能查询出改变量，即只过滤出改变过值的相邻记录。结果应如下：
1 开门
3 关门
4 开门
6 关门

-- 测试数据
DECLARE @t TABLE(
id int, value varchar(10))
INSERT @t SELECT 1, N'开门'
UNION ALL SELECT 2, N'开门'
UNION ALL SELECT 3, N'关门'
UNION ALL SELECT 4, N'开门'
UNION ALL SELECT 5, N'开门'
UNION ALL SELECT 6, N'关门'

-- 查询
SELECT *
FROM @t A
WHERE value <> ISNULL((
SELECT TOP 1 value FROM @t
WHERE id < A.id
ORDER BY id DESC), A.value + 'a')

-- 结果:
id value
----------- ----------
1 开门
3 关门
4 开门
6 关门

(4 行受影响)

[子陌红尘]

declare @t table(no int,flag varchar(4))
insert into @t select 1,'Y'
insert into @t select 2,'N'
insert into @t select 3,'N'
insert into @t select 4,'Y'
insert into @t select 5,'N'
insert into @t select 6,'Y'

--查询
select no,(select count(1) from @t where no<=a.no and flag='Y') as grp from @t a
/*
no grp
----------- -----------
1 1
2 1
3 1
4 2
5 2
6 3
*/

--更新
update a
set
flag=(select count(1) from @t where no<=a.no and flag='Y')
from
@t a

select * from @t
/*
no flag
----------- ----
1 1
2 1
3 1
4 2
5 2
6 3
*/

[leo_lesley]

update tablename
set
flag=(select count(1) from tablename where no<=tablename.no and flag='Y')