求助查询数据库里最长连续不等于0和999.9的数据

woshishi_456 其他  2015-07-23 08:25:04
数据库里是这样的,

我要查询rs里最长连续不等于0和999.9的数据加起来有几行。就是例如,0和999.9是晴天,其他的都有雨,我要查连续降雨日数。
求助这样的查询语句应该怎么写
...全文
360 点赞 收藏 9
写回复
9 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
许晨旭 2015-07-23
把这个改一下就好了
where RS is null
回复
woshishi_456 2015-07-23
引用 3 楼 Landa_Ran 的回复:
;with cte as(
select ID-ROW_NUMBER() over(order by getdate()) as row,ID,RS
from 
(select ID,
	case RS
		WHEN 0.0 THEN NULL
		WHEN 999.9 THEN NULL
		ELSE RS
	end  AS RS
from @tb) a
where RS is not null
),cte2 as(
select
	COUNT(*) over(partition by row) as [连续总计],
	ID,RS
from cte
)

select * 
from cte2
where [连续总计]=(select MAX([连续总计]) from cte2)
抱歉抱歉,还有一个问题,自己改了半天没改对,那要求rs等于0和999.9的连续总计要怎么求呢。
回复
woshishi_456 2015-07-23
引用 3 楼 Landa_Ran 的回复:
;with cte as(
select ID-ROW_NUMBER() over(order by getdate()) as row,ID,RS
from 
(select ID,
	case RS
		WHEN 0.0 THEN NULL
		WHEN 999.9 THEN NULL
		ELSE RS
	end  AS RS
from @tb) a
where RS is not null
),cte2 as(
select
	COUNT(*) over(partition by row) as [连续总计],
	ID,RS
from cte
)

select * 
from cte2
where [连续总计]=(select MAX([连续总计]) from cte2)
十分感谢!!弄出来了,谢谢谢谢!
回复
许晨旭 2015-07-23
;with cte as(
select ID-ROW_NUMBER() over(order by getdate()) as row,ID,RS
from 
(select ID,
	case RS
		WHEN 0.0 THEN NULL
		WHEN 999.9 THEN NULL
		ELSE RS
	end  AS RS
from @tb) a
where RS is not null
),cte2 as(
select
	COUNT(*) over(partition by row) as [连续总计],
	ID,RS
from cte
)

select * 
from cte2
where [连续总计]=(select MAX([连续总计]) from cte2)
回复
woshishi_456 2015-07-23
引用 1 楼 Landa_Ran 的回复:
declare @tb table
(
	ID INT IDENTITY(1,1),
	RS NUMERIC(10,1)
)

INSERT INTO @tb
select 0 union all
select 13.9 union all
select 49.1 union all
select 6.4 union all
select 3.4 union all
select 0.3 union all
select 999.9 union all
select 2.9 union all
select 1.2 union all
select 0 union all
select 0 union all
select 25.9 union all
select 0.5 union all
select 0 union all
select 0 union all
select 0 union all
select 0 union all
select 0.3 union all
select 9.6 union all
select 7.2 union all
select 13.5 union all
select 999.9 


;with cte as(
select ID-ROW_NUMBER() over(order by getdate()) as row,ID,RS
from 
(select ID,
	case RS
		WHEN 0.0 THEN NULL
		WHEN 999.9 THEN NULL
		ELSE RS
	end  AS RS
from @tb) a
where RS is not null
)

select
	COUNT(*) over(partition by row) as [连续总计],
	ID,RS
from cte
连续总计        ID          RS
----------- ----------- ---------------------------------------
5           2           13.9
5           3           49.1
5           4           6.4
5           5           3.4
5           6           0.3
2           8           2.9
2           9           1.2
2           12          25.9
2           13          0.5
4           18          0.3
4           19          9.6
4           20          7.2
4           21          13.5
抱歉抱歉,刚刚开会去了,那请问怎么从连续统计中截取出连续时间最长的那一段呢,比如上面就是需要截取出连续统计是5的那一串
回复
许晨旭 2015-07-23
declare @tb table
(
	ID INT IDENTITY(1,1),
	RS NUMERIC(10,1)
)

INSERT INTO @tb
select 0 union all
select 13.9 union all
select 49.1 union all
select 6.4 union all
select 3.4 union all
select 0.3 union all
select 999.9 union all
select 2.9 union all
select 1.2 union all
select 0 union all
select 0 union all
select 25.9 union all
select 0.5 union all
select 0 union all
select 0 union all
select 0 union all
select 0 union all
select 0.3 union all
select 9.6 union all
select 7.2 union all
select 13.5 union all
select 999.9 


;with cte as(
select ID-ROW_NUMBER() over(order by getdate()) as row,ID,RS
from 
(select ID,
	case RS
		WHEN 0.0 THEN NULL
		WHEN 999.9 THEN NULL
		ELSE RS
	end  AS RS
from @tb) a
where RS is not null
)

select
	COUNT(*) over(partition by row) as [连续总计],
	ID,RS
from cte
连续总计        ID          RS
----------- ----------- ---------------------------------------
5           2           13.9
5           3           49.1
5           4           6.4
5           5           3.4
5           6           0.3
2           8           2.9
2           9           1.2
2           12          25.9
2           13          0.5
4           18          0.3
4           19          9.6
4           20          7.2
4           21          13.5
回复
woshishi_456 2015-07-23
引用 6 楼 Landa_Ran 的回复:
把这个改一下就好了
where RS is null
.....................................不对,,,我自己想的那个多试了几组更错,,,,
回复
woshishi_456 2015-07-23
引用 6 楼 Landa_Ran 的回复:
把这个改一下就好了
where RS is null
我明白了,我想到怎么改了,谢谢大神,这次真的没有问题了,辛苦了
回复
woshishi_456 2015-07-23
引用 6 楼 Landa_Ran 的回复:
把这个改一下就好了
where RS is null


似乎看懂大神代码的意思了。改过来了,但是似乎这样改了之后,会有一点小小的错,就是0和999.9的rs会连在一起,日期不连续了


比较蠢的我自己试着改了一个小时没改出来,,,,抱歉再次来麻烦大神~~~

回复
相关推荐
发帖
疑难问题
创建于2007-09-28

2.1w+

社区成员

MS-SQL Server 疑难问题
申请成为版主
帖子事件
创建了帖子
2015-07-23 08:25
社区公告
暂无公告