--查询
select 月,最长连续无降水天数=max(a)
from(
select 月,a=(
select min(日)
from t1 aa
where 月=a.月 and 日>a.日 and 降水=0
and not exists(
select * from t1
where 月=aa.月 and 日=aa.日+1 and 降水=0)
)-日+1
from t1 a
where 降水=0 and not exists(
select * from t1
where 月=a.月 and 日=a.日-1 and 降水=0)
)a group by 月
select 月,最长连续无降水天数=max(a)
from(
select 月,a=(
select min(日)
from t1 aa
where 月=a.月 and 日>a.日 and 降水=0
and not exists(
select * from t1
where 月=aa.月 and 日=aa.日+1 and 降水=0)
)-日+1
from t1 a
where 降水=0 and not exists(
select * from t1
where 月=a.月 and 日=a.日-1 and 降水=0)
)a group by 月
解决方案 »
- 假设数据库不支持Access数据导入,只支持JDBC、ODBC、等数据源连接,那么怎么才能把它导入呢(不准用编程方法)
- SQL 2000
- 求一SQL语句????谢谢
- SQL Server 2000 问题(ODBC) ---在线等--- 急急急急急急急急
- sql 动态表头
- SQL 聚合语句问题 AVG
- 关于包的执行问题!!
- 关于字符串排序
- 请问如何查找a表中有,而在b表中没有的记录?
- 各视图或函数中的列名必须唯一。在视图或函数 'veb_book_publisher_category' 中多次指定了列名 'publisherId'。
- SQLServer7 数据库损坏,高手帮忙!
- 如果用一条sql语句,把一个表内,具有相同父类编号的子类别号的数量累加到父类编号上
create table t1(月 int,日 int,降水 decimal(10,1))
insert t1 select 3 ,1 ,0.5
union all select 3 ,2 ,0.6
union all select 3 ,3 ,1.3
union all select 3 ,4 ,0.5
union all select 3 ,5 ,0.0
union all select 3 ,6 ,0.0
union all select 3 ,7 ,0.0
union all select 3 ,8 ,3.5
union all select 3 ,9 ,1.6
union all select 3 ,10,0.2
union all select 3 ,11,0.6
union all select 3 ,12,0.0
union all select 3 ,13,0.0
union all select 3 ,14,0.2
union all select 3 ,15,0.2
union all select 3 ,16,0.1
union all select 3 ,17,0.1
union all select 3 ,18,0.0
union all select 3 ,19,0.0
union all select 3 ,20,5.2
union all select 3 ,21,0.0
union all select 3 ,22,0.0
union all select 3 ,23,0.0
union all select 3 ,24,0.0
union all select 3 ,25,0.0
union all select 3 ,26,0.1
union all select 3 ,27,0.0
union all select 3 ,28,0.0
union all select 3 ,29,0.6
union all select 3 ,30,0.0
union all select 3 ,31,0.0
go--查询
select 月,最长连续无降水天数=max(a)
from(
select 月,a=(
select min(日)
from t1 aa
where 月=a.月 and 日>a.日 and 降水=0
and not exists(
select * from t1
where 月=aa.月 and 日=aa.日+1 and 降水=0)
)-日+1
from t1 a
where 降水=0 and not exists(
select * from t1
where 月=a.月 and 日=a.日-1 and 降水=0)
)a group by 月go--删除测试
drop table t1/*--测试结果 月 最长连续无降水天数
----------- -----------
3 5(所影响的行数为 1 行)
--*/
create table tb(年 int,月 int ,降水 numeric(10,2))
Insert into tb
select '3','1','0.5'
union all select '3','2','0.6'
union all select '3','3','1.3'
union all select '3','4','0.5'
union all select '3','5','0.0'
union all select '3','6','0.0'
union all select '3','7','0.0'
union all select '3','8','3.5'
union all select '3','9','1.6'
union all select '3','10','0.2'
union all select '3','11','0.6'
union all select '3','12','0.0'
union all select '3','13','0.0'
union all select '3','14','0.2'
union all select '3','15','0.2'
union all select '3','16','0.1'
union all select '3','17','0.1'
union all select '3','18','0.0'
union all select '3','19','0.0'
union all select '3','20','5.2'
union all select '3','21','0.0'
union all select '3','22','0.0'
union all select '3','23','0.0'
union all select '3','24','0.0'
union all select '3','25','0.0'
union all select '3','26','0.1'
union all select '3','27','0.0'
union all select '3','28','0.0'
union all select '3','29','0.6'
union all select '3','30','0.0'
union all select '3','31','0.0'
--創建一個表,有個空字段
create table tb1(年 int,月 int ,降水 numeric(10,2),nday int)
insert into tb1(年,月,降水)
select * from tb order by 年,月select * from tb1declare @i int,@a numeric(10,2)
update tb1 set @i=(case @a when 0 then @i+1 else 0 end), nday=@i,@a= 降水--結果
月 日 降水 nday
----------------------------
3 1 .50 0
3 2 .60 0
3 3 1.30 0
3 4 .50 0
3 5 .00 0
3 6 .00 1
3 7 .00 2
3 8 3.50 3
3 9 1.60 0
3 10 .20 0
3 11 .60 0
3 12 .00 0
3 13 .00 1
3 14 .20 2
3 15 .20 0
3 16 .10 0
3 17 .10 0
3 18 .00 0
3 19 .00 1
3 20 5.20 2
3 21 .00 0
3 22 .00 1
3 23 .00 2
3 24 .00 3
3 25 .00 4
3 26 .10 5
3 27 .00 0
3 28 .00 1
3 29 .60 2
3 30 .00 0
3 31 .00 1
select 最長天數=max(nday) from tb1結果:5
alter table t1 add 连续无降水天数 int
go
declare @days int
set @days=0
update t1 set @days=case 降水 when 0 then @days+1 else 0 end,连续无降水天数=@days
select 月,max(连续无降水天数) 连续无降水天数 from t1 group by 月
go
alter table t1 drop column 连续无降水天数
declare @i int,@a numeric(10,2)
update tb1 set @a= 降水,@i=(case @a when 0 then @i+1 else 0 end), nday=@i--結果
月 日 降水 nday
----------------------------
3 1 .50 0
3 2 .60 0
3 3 1.30 0
3 4 .50 0
3 5 .00 1
3 6 .00 2
3 7 .00 3
3 8 3.50 0
3 9 1.60 0
3 10 .20 0
3 11 .60 0
3 12 .00 1
3 13 .00 2
3 14 .20 0
3 15 .20 0
3 16 .10 0
3 17 .10 0
3 18 .00 1
3 19 .00 2
3 20 5.20 0
3 21 .00 1
3 22 .00 2
3 23 .00 3
3 24 .00 4
3 25 .00 5
3 26 .10 0
3 27 .00 1
3 28 .00 2
3 29 .60 0
3 30 .00 1
3 31 .00 2select 最長天數=max(nday) from tb1 where 月=3結果:5
insert into #t1 select
3, 1 ,0.5 union all select
3 , 2 ,0.6 union all select
3 , 3 , 1.3 union all select
3 , 4 , 0.5 union all select
3 , 5 , 0.0 union all select
3 , 6 , 0.0 union all select
3 , 7 , 0.0 union all select
3 , 8 , 3.5 union all select
3 , 9 , 1.6 union all select
3 , 10 , 0.2 union all select
3 , 11 , 0.6 union all select
3 ,12, 0.0 union all select
3, 13 ,0.0 union all select
3 , 14 ,0.2 union all select
3 , 15 , 0.2 union all select
3 , 16 , 0.1 union all select
3 , 17 , 0.1 union all select
3 , 18 , 0.0 union all select
3 , 19 , 0.0 union all select
3 , 20 , 5.2 union all select
3 , 21 , 0.0 union all select
3 , 22 , 0.0 union all select
3 , 23 , 0.0 union all select
3 ,24, 0.0 union all select
3, 25 , 0.0 union all select
3 , 26 , 0.1 union all select
3 , 27 , 0.0 union all select
3 , 28 , 0.0 union all select
3 , 29 , 0.6 union all select
3 , 30 , 0.0 union all select
3 , 31, 0.0select max(da-d)+1 from (
select d,
da=(select isnull(max(d),a.d) from #t1 b
where d>a.d and not exists
(select 1 from #t1 where jy>0 and d between a.d and b.d))
from #t1 a where jy=0 ) a
/*
-----------
5(所影响的行数为 1 行)
*/