求助查询数据库里最长连续不等于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 的回复: