用一条SQL语句查询连续记录中最大个数,该怎么处理

用一条SQL语句查询连续记录中最大个数
ID   RESULT   DT
1    大            2012-10-01 10:01:00
2    大            2012-10-01 10:02:00
3    小            2012-10-01 10:03:00
4    大            2012-10-01 10:04:00
5    小            2012-10-01 10:05:00
6    小            2012-10-01 10:06:00
7    小            2012-10-01 10:07:00
8    小            2012-10-01 10:08:00
9    大            2012-10-01 10:09:00
10   大           2012-10-01 10:10:00
-----------------------------------
如何得到时间在2012-10-01 10:01:00和2012-10-01 10:09:00间
连续相同的RESULT的最大个数？用一条SQL语句
连续的个数如：
大2
小1
大1
小4
大1
最终得到：4
[解决办法]

--建立测试
CREATE TABLE #CESHI (ID varchar(10),RESULT  varchar(10),dt DATETIME) 

--插入数据
INSERT INTO #CESHI
SELECT '1','大','2012-10-01 10:01:00'
UNION 
SELECT '2','大','2012-10-01 10:02:00'
UNION 
SELECT'3','小', '2012-10-01 10:03:00'
UNION 
SELECT'4','大', '2012-10-01 10:04:00'
UNION 
SELECT'5','小', '2012-10-01 10:05:00'
UNION 
SELECT'6','小', '2012-10-01 10:06:00'
UNION 
SELECT'7','小', '2012-10-01 10:07:00'
UNION 
SELECT'8','小', '2012-10-01 10:08:00'
UNION 
SELECT'9','大', '2012-10-01 10:09:00'
UNION 
SELECT'10','大', '2012-10-01 10:10:00'

--排序
select *,num=row_number() over(order by getdate())into #tb from #CESHI order by dt

--取得数据
select result,count(1) as cnt into #jg
from(
select result,num,
gid=num-(select count(1) from #tb where result=t.result and num<t.num)
from #tb t
) a
group by result,gid
order by min(num)

--输出结果
select MAX(cnt)from #jg

--删除测试
drop table #CESHI
drop table #tb
drop table #jg

借鉴了下大神们的写法,希望对你有帮助
[解决办法]

--排序
select *,num=row_number() over(order by getdate())into #tb from #CESHI order by dt

改为

;with #tb as
(select *,num=row_number() over(order by getdate()) from #CESHI )

..能力有限,不好意思..想不出在不取出结果的情况下,如何直接输出结果4...
这样改也只能是省了一步插入临时表的步骤..
[解决办法]

--建立测试
CREATE TABLE #CESHI (ID varchar(10),RESULT  varchar(10),dt DATETIME)  
 
 
--插入数据
INSERT INTO #CESHI
SELECT '1','大' ,'2012-10-01 10:01:00'
UNION 
SELECT '2','大','2012-10-01 10:02:00'
UNION 
SELECT'3','小', '2012-10-01 10:03:00'
UNION 
SELECT'4','大', '2012-10-01 10:04:00'
UNION 
SELECT'5','小', '2012-10-01 10:05:00'
UNION 
SELECT'6','小', '2012-10-01 10:06:00'
UNION 
SELECT'7','小', '2012-10-01 10:07:00'
UNION 
SELECT'8','小', '2012-10-01 10:08:00'
UNION 
SELECT'9','大', '2012-10-01 10:09:00'
UNION 
SELECT'10','大', '2012-10-01 10:10:00'

--建立存储过程
  CREATE PROCEDURE Demo 
  (
@Begindate datetime,
@Enddate   datetime
   )
as
begin
select *,num=row_number() over(order by getdate())into #tb from #CESHI where dt between @Begindate and @Enddate order by dt
 
--取得数据
select result,count(1) as cnt into #jg
from(
select result,num,
gid=num-(select count(1) from #tb where result=t.result and num<t.num)
from #tb t
) a
group by result,gid
order by min(num)
 
--输出结果
select MAX(cnt)from #jg

end 

--测试结果
exec Demo '2012-10-01 10:01:00','2012-10-01 10:10:00'

这样把语句加成一个存储过程,以后要查询的时候直接修改参数的数值就可以了.
[解决办法]
貌似楼上已经很简单了。来个复杂点得吧！

if OBJECT_ID('test') is not null drop table test
--建立测试
CREATE TABLE test(id int identity(1,1),RESULT  varchar(10),dt DATETIME) 
go
--插入数据
INSERT INTO test(RESULT,dt)
SELECT '大' ,'2012-10-01 10:01:00' union all
SELECT '大','2012-10-01 10:02:00' union all
SELECT '小', '2012-10-01 10:03:00' union all
SELECT '大', '2012-10-01 10:04:00' union all
SELECT '小', '2012-10-01 10:05:00' union all
SELECT '小', '2012-10-01 10:06:00' union all
SELECT '小', '2012-10-01 10:07:00' union all
SELECT '小', '2012-10-01 10:08:00' union all
SELECT '大', '2012-10-01 10:09:00' union all
SELECT '大', '2012-10-01 10:10:00'

;with T as(
select id,case when RESULT='大' then 0 else 1  end as RESULT from test
where dt<'2012-10-01 10:10:00' and  dt>='2012-10-01 10:01:00'
)
Select top 1 
IsNull((Select Min(id) From T Where RESULT != A.RESULT And ID > A.ID), (Select Max(id) From T) + 1) - A.id As [Count]
From T A
Where Exists (Select id From T Where RESULT != A.RESULT And id = A.id - 1)
Or A.id = 1 order by [Count] desc 

DROP TABLE test

查看更多 下一篇