用一条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