create function HoursGettxtZDZ1(@id varchar(10),@times1 varchar(20),@times2 varchar(20),@Realtimes varchar(20))
returns varchar(8000)
as
begin
declare @s int
set @s=(
select sum(cast([R1H] as int)) from aws_minute_rain_history
where StationID=@id
and Date+time>@times1 and Date+time<=@times2
)+
(select cast(dbo.Minute_RealR([Rain60min]) as int) from aws_minute_rain_Real
where StationID=@id
and Date+time=@Realtimes)--如果没有结果则为空,而我想没有结果则为0,然后跟上一句相加
--因为30+null=null
--if dbo.Minute_RealR([Rain60min]) =null then dbo.Minute_RealR([Rain60min])=0
return @s
end
现在出现这个问题:当 @Realtimes没有数据时即Null时,则上一句+null=null,我想得结果为上一句的结果+0=上一句的结果
returns varchar(8000)
as
begin
declare @s int
set @s=(
select sum(cast([R1H] as int)) from aws_minute_rain_history
where StationID=@id
and Date+time>@times1 and Date+time<=@times2
)+
(select cast(dbo.Minute_RealR([Rain60min]) as int) from aws_minute_rain_Real
where StationID=@id
and Date+time=@Realtimes)--如果没有结果则为空,而我想没有结果则为0,然后跟上一句相加
--因为30+null=null
--if dbo.Minute_RealR([Rain60min]) =null then dbo.Minute_RealR([Rain60min])=0
return @s
end
现在出现这个问题:当 @Realtimes没有数据时即Null时,则上一句+null=null,我想得结果为上一句的结果+0=上一句的结果
returns varchar(8000)
as
begin
declare @s int
set @s=(
select sum(cast([R1H] as int)) from aws_minute_rain_history
where StationID=@id
and Date+time>@times1 and Date+time<=@times2
)+
(select isnull(cast(dbo.Minute_RealR([Rain60min]) as int),0) from aws_minute_rain_Real
where StationID=@id
and Date+time=@Realtimes)--如果没有结果则为空,而我想没有结果则为0,然后跟上一句相加
--因为30+null=null
--if dbo.Minute_RealR([Rain60min]) =null then dbo.Minute_RealR([Rain60min])=0
return @s
end
还是有Null,如果Date+time=@Realtimes的数据不为空,则是对的
-- 可能是别的原因了create table test
(
Rain60min varchar(10)
)
insert test
select '20' union all
select '70'
select * from testdeclare @s int
select @s=(select isnull(sum(cast(Rain60min as int)),0) from test where Rain60min>100)
select @sdrop table test