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

[woshishi_456]

数据库里是这样的，

我要查询rs里最长连续不等于0和999.9的数据加起来有几行。就是例如，0和999.9是晴天，其他的都有雨，我要查连续降雨日数。
求助这样的查询语句应该怎么写

[woshishi_456]

引用 6 楼 Landa_Ran 的回复:

把这个改一下就好了

where RS is null

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


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

• 打赏
• 举报

回复 赞

[woshishi_456]

引用 6 楼 Landa_Ran 的回复:

把这个改一下就好了

where RS is null

我明白了，我想到怎么改了，谢谢大神，这次真的没有问题了，辛苦了

• 打赏
• 举报

回复 赞

[woshishi_456]

引用 6 楼 Landa_Ran 的回复:

把这个改一下就好了

where RS is null

.....................................不对，，，我自己想的那个多试了几组更错，，，，

• 打赏
• 举报

回复 赞

[许晨旭]

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]

引用 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的那一串

• 打赏
• 举报

回复 赞

[许晨旭]

;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]

引用 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)

十分感谢！！弄出来了，谢谢谢谢！

• 打赏
• 举报

回复 赞

[woshishi_456]

引用 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的连续总计要怎么求呢。

• 打赏
• 举报

回复 赞

[许晨旭]

把这个改一下就好了

where RS is null

• 打赏
• 举报

回复 赞