24个小时时段，计算时差，判断字段相加

net_xiaojian 2010-07-14 12:46:58

--表结构

create table tb

(

id int

,TM datetime

,p8

,p9

...

,p23

,p0

...

,p7

) --p8--p7为24个小时段雨量



--功能

界面上时间选择 2010-7-10 12 ----2010-7-14 01 ，求累计雨量



declare @begin datetime	--开始时间

declare @end  datetime		--结束时间

declare @bh int			--起始小时

declare @eh int			--截止小时

declare @num float

--记录起止小时段

set @begin='2010-7-13 21:18:39' set @end='2010-7-14 11:18:59'



select @bh=datepart(hh,@begin)   --获取指定日期部分的整数

select @eh=datepart(hh,@end)



--计算小时时差，

select case when ((datediff(hour,@begin,@end))-(datediff(d,@begin,@end)*24))<0 

		then (24+((datediff(hour,@begin,@end))-(datediff(d,@begin,@end)*24))) 

	     when ((datediff(hour,@begin,@end))-(datediff(d,@begin,@end)*24))>0

		then (datediff(hour,@begin,@end))-(datediff(d,@begin,@end)*24)

	else 0 end as 'number'

begin

select a.stcd



while number>0

begin

	set @num=@num+p   --循环这里怎么做



from st_rain_s a and tm between @begin and @end

group by a.stcd

end



--数据显示

stcd         累计雨量       ...

--循环那里怎么做，主要是24小时字段p8,p9,..,p23,p0,..p7这种形式的。

...全文

265 15 打赏收藏转发到动态举报

写回复

用AI写文章

15 条回复

切换为时间正序

请发表友善的回复…

发表回复

net_xiaojian 2010-07-15

打赏
举报

有什么方法让sql2000代替 unpivot ？

永生天地 2010-07-14

打赏
举报

路过帮顶

net_xiaojian 2010-07-14

打赏
举报

to lakesy

sql2000不认unpivot。

Lakesy 2010-07-14

打赏
举报

哈?不好意思.我的是2008

Lakesy 2010-07-14

打赏
举报

sorry,这样才对,case when 出错了



declare @table table(id int ,TM datetime,

	p8 int,

	p9 int,

	p10 int,

	p11 int,

	p12 int,

	p13 int,

	p14 int,

	p15 int,

	p16 int,

	p17 int,

	p18 int,

	p19 int,

	p20 int,

	p21 int,

	p22 int,

	p23 int,

	p24 int,

	p1 int,

	p2 int,

	p3 int,

	p4 int,

	p5 int,

	p6 int,

	p7 int

	)

insert into @table

select 119,'2010-07-14 0:0:0',8008,9009,100010,110011,120012,130013,140014,150015,160016,170017,180018,190019,200020,210021,220022,230023,240024,1001,2002,3003,4004,5005,6006,7007



select * from @table

-----------------------------

declare @date datetime='2010-07-14'

declare @SHour int=9

declare @EHour int=10



select SUM(value) as result from(

	select case

		when col='p8' then  8 

		when col='p9' then  9 

		when col='p10' then 10

		when col='p11' then 11

		when col='p12' then 12

		when col='p13' then 13

		when col='p14' then 14

		when col='p15' then 15

		when col='p16' then 16

		when col='p17' then 17

		when col='p18' then 18

		when col='p19' then 19

		when col='p20' then 20

		when col='p21' then 21

		when col='p22' then 22

		when col='p23' then 23

		when col='p24' then 24

		when col='p1' then 1

		when col='p2' then 2

		when col='p3' then 3

		when col='p4' then 4

		when col='p5' then 5

		when col='p6' then 6

		when col='p7' then 7

		end as HHour,*

	from 

	@table

	 as t

	unpivot

	(

		value

		for col in([p1],[p2],[p3],[p4],[p5],[p6],[p7],[p8],[p9],[p10],[p11],[p12],[p13],[p14],[p15],[p16],[p17],[p18],[p19],[p20],[p21],[p22],[p23],[p24])

	)as tp

) as tTotal

where HHour between @SHour and @EHour



------------

net_xiaojian 2010-07-14

打赏
举报

to Lakesy

我的是sql2000

Lakesy 2010-07-14

打赏
举报

不知这样是否满足楼主想法:



declare @table table(id int ,TM datetime,

	p8 int,

	p9 int,

	p10 int,

	p11 int,

	p12 int,

	p13 int,

	p14 int,

	p15 int,

	p16 int,

	p17 int,

	p18 int,

	p19 int,

	p20 int,

	p21 int,

	p22 int,

	p23 int,

	p24 int,

	p1 int,

	p2 int,

	p3 int,

	p4 int,

	p5 int,

	p6 int,

	p7 int

	)

insert into @table

select 119,'2010-07-14 0:0:0',1001,2002,3003,4004,5005,6006,7007,8008,9009,100010,110011,120012,130013,140014,150015,160016,170017,180018,190019,200020,210021,220022,230023,240024



select * from @table

-----------------------------

declare @date datetime='2010-07-14'

declare @SHour int=9

declare @EHour int=13



select SUM(value) as result from(

	select case

		when col='p8' then 1

		when col='p9' then 2

		when col='p10' then 3

		when col='p11' then 4

		when col='p12' then 5

		when col='p13' then 6

		when col='p14' then 7

		when col='p15' then 8

		when col='p16' then 9

		when col='p17' then 10

		when col='p18' then 11

		when col='p19' then 12

		when col='p20' then 13

		when col='p21' then 14

		when col='p22' then 15

		when col='p23' then 16

		when col='p24' then 17

		when col='p1' then 18

		when col='p2' then 19

		when col='p3' then 20

		when col='p4' then 21

		when col='p5' then 22

		when col='p6' then 23

		when col='p7' then 24

		end as HHour,*

	from 

	@table

	 as t

	unpivot

	(

		value

		for col in([p1],[p2],[p3],[p4],[p5],[p6],[p7],[p8],[p9],[p10],[p11],[p12],[p13],[p14],[p15],[p16],[p17],[p18],[p19],[p20],[p21],[p22],[p23],[p24])

	)as tp

) as tTotal

where HHour between @SHour and @EHour

net_xiaojian 2010-07-14

打赏
举报



declare @begin datetime                        --开始时间

declare @end  datetime                --结束时间

declare @num float

declare @num1 float

declare @bh int                        --起始小时

declare @eh int                         --截止小时

declare @h int

declare @strsql varchar(4000)



declare @s1 nvarchar(4000)

declare @s2 nvarchar(4000)



set @begin='2010-7-1 9:18:39' set @end='2010-7-14 11:18:59'

--需要判断时间 输入是否准确



select @bh=datepart(hh,@begin)

select @eh=datepart(hh,@end)

print @bh

print @eh



--将日期内的数据求和

select @num = sum(isnull(p8,0))+sum(isnull(p9,0))+sum(isnull(p10,0))+sum(isnull(p11,0))+sum(isnull(p12,0))+sum(isnull(p13,0))+sum(isnull(p14,0))+

sum(isnull(p15,0))+sum(isnull(p16,0))+sum(isnull(p17,0))+sum(isnull(p18,0))+sum(isnull(p19,0))+sum(isnull(p20,0))+sum(isnull(p21,0))+sum(isnull(p2,0))+

sum(isnull(p23,0))+sum(isnull(p0,0))+sum(isnull(p1,0))+sum(isnull(p2,0))+sum(isnull(p3,0))+sum(isnull(p4,0))+sum(isnull(p5,0))+sum(isnull(p6,0))+sum(isnull(p7,0)) 

from st_rain_s where tm between convert(varchar(10),@begin,120) and convert(varchar(10),@end,120)



print @num

set @s1='select @num1 = '

if(@bh<@eh)

begin

	while @bh<@eh

		begin 

			set @s1=@s1+'p'+convert(varchar(2),@bh)+' +'

			set @bh=@bh+1

		end

	set @s1 = left(@s1 ,len(@s1)-1) + ' from st_rain_s where tm = '''+ convert(varchar(10),@begin,120) +''''

	exec sp_executesql @s1

end

提示：必须声明变量 '@num1'。我上面不是声明了吗

天-笑 2010-07-14

打赏
举报



set @s1=@s1+'p'+convert(varchar(2),@bh)+' +'

net_xiaojian 2010-07-14

打赏
举报



set @s1='select @num1='

if(@bh<@eh)

begin

	while @bh<@eh

		begin 

			@s1=@s1+'p'+convert(varchar(2),@bh)+' +'

			set @bh=@bh+1

		end

	set @s1 = left(@s1 ,len(@s1)-1) + ' from st_rain_s where tm = '''+ convert(varchar(10),@begin,120) +''''

	exec sp_executesql @s1

end

参数都定义了，提示说 @s1=@s1+'p'+convert(varchar(2),@bh)+' +'
附近有语法错误，

net_xiaojian 2010-07-14

打赏
举报

选择时间段比如：
开始时间：2010-7-10 07 截止时间：2010-7-14 10

那么就是字段：p7+p8+p9+p10的和

开始时间：2010-7-10 21 截止时间：2010-7-14 02

那么就是字段：p21+p22+p23+p0+p1+p2

明白了吗？

hahaxiaoa 2010-07-14

打赏
举报

有点迷糊.

htl258_Tony 2010-07-14

打赏
举报

楼主到底是什么问题，看了许久，没真正理解到底要什么？

天-笑 2010-07-14

打赏
举报

上面的有错



@begin datetime						--开始时间

declare @end  datetime				--结束时间

declare @num float

declare @num1 float

declare @bh int						--起始小时

declare @eh int						 --截止小时

declare @h int

declare @strsql varchar(4000)





set @begin='2010-7-13 21:18:39' set @end='2010-7-14 11:18:59'

--需要判断时间 输入是否准确



--将日期内的数据求和

select @num = sum(P8)+......sum(P7) from tb where tm >= convert(varchar(10),@begin,120) and tm <= convert(varchar(10),@end,120)

--截头 将0点到 21点的 求和减掉

select @bh=datepart(hh,@begin)   --获取指定日期部分的整数

set @h = 0

set @strsql = 'select @num1 = '

while @h<@bh

	@strsql = @strsql + 'P'+convert(varchar(2),@h) + ' +'

	set @h = @h + 1

end

set @strsql = left(@strsql ,len(@strsql)-1) + ' from tb where tm = '''+ convert(varchar(10),@begin,120) +''''



--执行上面的动态字符串 将结果输出



set @num = @num - @num1

--去尾 将 11点到 23点的 求和减掉 同上

select @eh=datepart(hh,@end) 



--最后return @num1 

select @num1

天-笑 2010-07-14

打赏
举报



@begin datetime						--开始时间

declare @end  datetime				--结束时间

declare @num float

declare @num1 float

declare @bh int						--起始小时

declare @eh int						 --截止小时

declare @h int

declare @strsql varchar(4000)





set @begin='2010-7-13 21:18:39' set @end='2010-7-14 11:18:59'

--判断时间 输入是否准确



--将日期内的数据求和

select @num = sum(P8)+......sum(P7) from tb where tm >= convert(varchar(10),@begin,120) and tm <= convert(varchar(10),@end,120)

--截头 将0点到 21点的 求和减掉

select @bh=datepart(hh,@begin)   --获取指定日期部分的整数

set @h = 0

set @strsql = 'select @num1 = '

while @h<@bh

	@strsql = @strsql + 'P'+convert(varchar(2),@h) + ' +'

	set @h = @h + 1

end

set @strsql = left(@strsql ,len(@strsql)-1) + ' from tb where tm = '''+ convert(varchar(10),@begin,120) +''''



--执行上面的动态字符串 将结果输出



set @num = @num1

--去尾 将 11点到 23点的 求和减掉 同上

select @eh=datepart(hh,@end) 



--最后return @num1 

select @num1