表1:
字段1,字段2
1 1
2 1
3 1
4 1
5 2
6 1
7 1
8 2
9 1
10 1如上两列,我想查找出连续的字段1,查找出连续的次数,我想统计的结果应该是4,而不是cout的8
这样的SQL怎么写呢?
字段1,字段2
1 1
2 1
3 1
4 1
5 2
6 1
7 1
8 2
9 1
10 1如上两列,我想查找出连续的字段1,查找出连续的次数,我想统计的结果应该是4,而不是cout的8
这样的SQL怎么写呢?
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-11-06 15:42:43
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([value1] int,[value2] int)
insert [tb]
select 1,1 union all
select 2,1 union all
select 3,1 union all
select 4,1 union all
select 5,2 union all
select 6,1 union all
select 7,1 union all
select 8,2 union all
select 9,1 union all
select 10,1
--------------开始查询--------------------------
select id=identity(int,1,1),* into # from tb
select
value1,value2,col3=(select count(1) from # where [value2]=a.[value2] and id<=a.id
and id>=(select isnull(max(id),0) from # where id<a.id and value2!=a.value2))
from # a
drop table #
----------------结果----------------------------
/* value1 value2 col3
----------- ----------- -----------
1 1 1
2 1 2
3 1 3
4 1 4
5 2 1
6 1 1
7 1 2
8 2 1
9 1 1
10 1 2(10 行受影响)*/
as
(select rid=row_number() over (order by col1), * from ta where col2 = 1)
select top 1 count(1)
from t1
group by col1 - rid
order by 1 desc
--Result:
/*
-----------
4(1 行受影响)
*/
--End