declare @tb table
(
月 int,日 int,T decimal(12,1),S decimal(12,1)
)
insert @tb
select 4,1,26.7,6.3 union
select 4,2,19.9,0 union
select 4,3,16.5,0 union
select 4,4,15.9,0 union
select 4,5,21.4,2.1 union
select 4,6,24.9,7.8 union
select 4,7,25.1,9.8 union
select 4,8,24.9,7.9 union
select 4,9,24.9,7.7 union
select 4,10,24.4,1.1 union
select 4,11,22.0,0 union
select 4,12,21.6,0 union
select 4,13,23.3,1.3 union
select 4,14,23.0,2.7 union
select 4,15,24.2,3.5 union
select 4,16,24.2,.0 union
select 4,17,25.7,10.2 union
select 4,18,26.1,10.4 union
select 4,19,26.8,8.7 union
select 4,20,19.6,0 union
select 4,21,18.5,0 union
select 4,22,18.5,0 union
select 4,23,19.2,2.3 union
select 4,24,20.6,0.4 union
select 4,25,22.1,6.5 union
select 4,26,23.5,7.1 union
select 4,27,23.7,5.9 union
select 4,28,24.7,8.7 union
select 4,29,25.4,7.8 union
select 4,30,25.2,1.7
--查询
select convert(varchar,月)+'月'
+convert(varchar,日)+'日-'
+convert(varchar
,(select min(日) from @tb tb where 日>t.日 and (T>22 or S>2))
)+'日' as '日期'
,(select min(日)
from @tb tb
where 日>t.日 and (T>22 or S>2))-日+1 as '连续天数'
,convert(decimal(12,1),
(select avg(T)
from @tb
where 日 between t.日
and (select min(日)
from @tb tb
where 日>t.日 and (T>22 or S>2)))) as '平均T'
from @tb t
where exists(select 1 from @tb where 月=t.月 and 日=t.日+1 and T<=22 and S<=2)
and
exists(select 1 from @tb where 月=t.月 and 日=t.日+2 and T<=22 and S<=2)
and
T<=22
and
S<=2--结果
/*
日期 连续天数 平均T
------------------------------------------------
4月2日-5日 4 18.4
4月20日-23日 4 19.0(所影响的行数为 2 行)
*/
(
月 int,日 int,T decimal(12,1),S decimal(12,1)
)
insert @tb
select 4,1,26.7,6.3 union
select 4,2,19.9,0 union
select 4,3,16.5,0 union
select 4,4,15.9,0 union
select 4,5,21.4,2.1 union
select 4,6,24.9,7.8 union
select 4,7,25.1,9.8 union
select 4,8,24.9,7.9 union
select 4,9,24.9,7.7 union
select 4,10,24.4,1.1 union
select 4,11,22.0,0 union
select 4,12,21.6,0 union
select 4,13,23.3,1.3 union
select 4,14,23.0,2.7 union
select 4,15,24.2,3.5 union
select 4,16,24.2,.0 union
select 4,17,25.7,10.2 union
select 4,18,26.1,10.4 union
select 4,19,26.8,8.7 union
select 4,20,19.6,0 union
select 4,21,18.5,0 union
select 4,22,18.5,0 union
select 4,23,19.2,2.3 union
select 4,24,20.6,0.4 union
select 4,25,22.1,6.5 union
select 4,26,23.5,7.1 union
select 4,27,23.7,5.9 union
select 4,28,24.7,8.7 union
select 4,29,25.4,7.8 union
select 4,30,25.2,1.7
--查询
select convert(varchar,月)+'月'
+convert(varchar,日)+'日-'
+convert(varchar
,(select min(日) from @tb tb where 日>t.日 and (T>22 or S>2))
)+'日' as '日期'
,(select min(日)
from @tb tb
where 日>t.日 and (T>22 or S>2))-日+1 as '连续天数'
,convert(decimal(12,1),
(select avg(T)
from @tb
where 日 between t.日
and (select min(日)
from @tb tb
where 日>t.日 and (T>22 or S>2)))) as '平均T'
from @tb t
where exists(select 1 from @tb where 月=t.月 and 日=t.日+1 and T<=22 and S<=2)
and
exists(select 1 from @tb where 月=t.月 and 日=t.日+2 and T<=22 and S<=2)
and
T<=22
and
S<=2--结果
/*
日期 连续天数 平均T
------------------------------------------------
4月2日-5日 4 18.4
4月20日-23日 4 19.0(所影响的行数为 2 行)
*/
第一个结果应是4月2日-4日连续三天,4月5日不符合(2.1>2)
第二个结果应是4月20日-22日连续三天,4月23日不符合(2.3>2)
declare @tb table
(
月 int,日 int,T decimal(12,1),S decimal(12,1)
)
insert @tb
select 4,1,26.7,6.3 union
select 4,2,19.9,0 union
select 4,3,16.5,0 union
select 4,4,15.9,0 union
select 4,5,21.4,2.1 union
select 4,6,24.9,7.8 union
select 4,7,25.1,9.8 union
select 4,8,24.9,7.9 union
select 4,9,24.9,7.7 union
select 4,10,24.4,1.1 union
select 4,11,22.0,0 union
select 4,12,21.6,0 union
select 4,13,23.3,1.3 union
select 4,14,23.0,2.7 union
select 4,15,24.2,3.5 union
select 4,16,24.2,.0 union
select 4,17,25.7,10.2 union
select 4,18,26.1,10.4 union
select 4,19,26.8,8.7 union
select 4,20,19.6,0 union
select 4,21,18.5,0 union
select 4,22,18.5,0 union
select 4,23,19.2,2.3 union
select 4,24,20.6,0.4 union
select 4,25,22.1,6.5 union
select 4,26,23.5,7.1 union
select 4,27,23.7,5.9 union
select 4,28,24.7,8.7 union
select 4,29,25.4,7.8 union
select 4,30,25.2,1.7
select *,identity(int,1,1) as id into # from @tb where [t]<=22 and [s]<=2
select [日期]=cast(b.月 as varchar)+'月'+cast(b.日 as varchar)+'-'
+cast(b.b1 as varchar)+'月'+cast(b.b2 as varchar)+'日',
[连续天数]=b.b2-b.日,
[平均T]=avg(a.t)
from @tb a,(select a.月,a.日,max(b.月) as b1,max(b.日) as b2
from # a,# b where b.日-a.日=b.id-a.id and b.id-a.id>=2 group by a.月,a.日) b
where cast(a.月 as varchar)+'-'+cast(a.日 as varchar)
between cast(b.月 as varchar)+'-'+cast(b.日 as varchar) and cast(b1 as varchar)+'-'+cast(b2 as varchar)
group by b.月,b.日,b.b1,b.b2
/*
日期 连续天数 平均T
--------------------------------
4月2-4月4日 2 20.950000
4月20-4月22日 2 18.866666
*/
drop table #
(
月 int,日 int,T decimal(12,1),S decimal(12,1)
)
insert @tb
select 4,1,26.7,6.3 union
select 4,2,19.9,0 union
select 4,3,16.5,0 union
select 4,4,15.9,0 union
select 4,5,21.4,2.1 union
select 4,6,24.9,7.8 union
select 4,7,25.1,9.8 union
select 4,8,24.9,7.9 union
select 4,9,24.9,7.7 union
select 4,10,24.4,1.1 union
select 4,11,22.0,0 union
select 4,12,21.6,0 union
select 4,13,23.3,1.3 union
select 4,14,23.0,2.7 union
select 4,15,24.2,3.5 union
select 4,16,24.2,.0 union
select 4,17,25.7,10.2 union
select 4,18,26.1,10.4 union
select 4,19,26.8,8.7 union
select 4,20,19.6,0 union
select 4,21,18.5,0 union
select 4,22,18.5,0 union
select 4,23,19.2,2.3 union
select 4,24,20.6,0.4 union
select 4,25,22.1,6.5 union
select 4,26,23.5,7.1 union
select 4,27,23.7,5.9 union
select 4,28,24.7,8.7 union
select 4,29,25.4,7.8 union
select 4,30,25.2,1.7
--查询
select convert(varchar,月)+'月'
+convert(varchar,日)+'日-'
+convert(varchar
,(select min(日) from @tb tb where 日>t.日 and (T>22 or S>2))-1
)+'日' as '日期'
,(select min(日)
from @tb tb
where 日>t.日 and (T>22 or S>2))-日 as '连续天数'
,convert(decimal(12,1),
(select avg(T)
from @tb
where 日 between t.日
and (select min(日)
from @tb tb
where 日>t.日 and (T>22 or S>2)))-1) as '平均T'
from @tb t
where exists(select 1 from @tb where 月=t.月 and 日=t.日+1 and T<=22 and S<=2)
and
exists(select 1 from @tb where 月=t.月 and 日=t.日+2 and T<=22 and S<=2)
and
T<=22
and
S<=2--结果
/*日期 连续天数 平均T
----------------------------------------------
4月2日-4日 3 17.4
4月20日-22日 3 18.0(所影响的行数为 2 行)
*/
declare @tb table
(
月 int,日 int,T decimal(12,1),S decimal(12,1)
)
insert @tb
select 4,1,26.7,6.3 union
select 4,2,19.9,0 union
select 4,3,16.5,0 union
select 4,4,15.9,0 union
select 4,5,21.4,2.1 union
select 4,6,24.9,7.8 union
select 4,7,25.1,9.8 union
select 4,8,24.9,7.9 union
select 4,9,24.9,7.7 union
select 4,10,24.4,1.1 union
select 4,11,22.0,0 union
select 4,12,21.6,0 union
select 4,13,23.3,1.3 union
select 4,14,23.0,2.7 union
select 4,15,24.2,3.5 union
select 4,16,24.2,.0 union
select 4,17,25.7,10.2 union
select 4,18,26.1,10.4 union
select 4,19,26.8,8.7 union
select 4,20,19.6,0 union
select 4,21,18.5,0 union
select 4,22,18.5,0 union
select 4,23,19.2,2.3 union
select 4,24,20.6,0.4 union
select 4,25,22.1,6.5 union
select 4,26,23.5,7.1 union
select 4,27,23.7,5.9 union
select 4,28,24.7,8.7 union
select 4,29,25.4,7.8 union
select 4,30,25.2,1.7
select *,identity(int,1,1) as id into # from @tb where [t]<=22 and [s]<=2
select [日期]=cast(b.月 as varchar)+'月'+cast(b.日 as varchar)+'-'
+cast(b.b1 as varchar)+'月'+cast(b.b2 as varchar)+'日',
[连续天数]=b.b2-b.日+1,
[平均T]=avg(a.t)
from @tb a,(select a.月,a.日,max(b.月) as b1,max(b.日) as b2
from # a,# b where b.日-a.日=b.id-a.id and b.id-a.id>=2 group by a.月,a.日) b
where cast('1900-'+cast(a.月 as varchar)+'-'+cast(a.日 as varchar) as datetime)
between cast('1900-'+cast(b.月 as varchar)+'-'+cast(b.日 as varchar) as datetime) and
cast('1900-'+cast(b1 as varchar)+'-'+cast(b2 as varchar) as datetime)
group by b.月,b.日,b1,b2
/*
日期 连续天数 平均T
---------------------------------------------
4月2-4月4日 3 17.433333
4月20-4月22日 3 18.866666
*/
drop table #