--赋值当前时间
declare @endtime datetime
declare @begintime datetime
select @endtime=getdate(),@begintime=CAST(CONVERT(VARCHAR(13),GETDATE(),120)+':00' AS DATETIME)SELECT cast(区站号 as char(8)),
cast(插入时间 as datetime),
'G',
cast(观测时间 as datetime),
cast(电池电压 as char(8)),
cast(瞬时风速 as int),
cast(瞬时风向 as int),
cast(二分钟平均风速*10 as int),
cast(二分钟平均风向 as int),
cast(十分钟平均风速*10 as int),
cast(十分钟平均风向 as int),
cast(极大风速*10 as int),
cast(极大风速的风向 as int),
cast(极大风速出现时间 as char(10)),
cast(最大风速*10 as int),
cast(最大风速的风向 as int),
cast(最大风速出现时间 as char(10)),
cast(小时每分钟雨量 as char(120)),
cast(小时雨量*10 as int),
NULL,
NULL,
cast(气温*10 as int),
cast(最高气温*10 as int),
cast(最高气温出现时间 as char(10)),
cast(最低气温*10 as int),
cast(最低气温出现时间 as char(10)),
cast(相对湿度 as int),
cast(最小相对湿度 as int),
cast(最小相对湿度出现时间 as char(10)),
cast(本站气压*10 as int),
cast(最高本站气压*10 as int),
cast(最高本站气压出现时间 as char(10)),
cast(最低本站气压*10 as int),
cast(最低本站气压出现时间 as char(10))
FROM openrowset('SQLOLEDB','172.19.18.133';'sa';'sa',Meso_2009.dbo.LN_2009)
where DATEPART(second, cast(观测时间 as dateTime))=0 and @begintime<观测时间 and 观测时间<@endtime
-----------------
我想计算相同区站号,雨量的合计
下面是查询数据结果
declare @endtime datetime
declare @begintime datetime
select @endtime=getdate(),@begintime=CAST(CONVERT(VARCHAR(13),GETDATE(),120)+':00' AS DATETIME)SELECT cast(区站号 as char(8)),
cast(插入时间 as datetime),
'G',
cast(观测时间 as datetime),
cast(电池电压 as char(8)),
cast(瞬时风速 as int),
cast(瞬时风向 as int),
cast(二分钟平均风速*10 as int),
cast(二分钟平均风向 as int),
cast(十分钟平均风速*10 as int),
cast(十分钟平均风向 as int),
cast(极大风速*10 as int),
cast(极大风速的风向 as int),
cast(极大风速出现时间 as char(10)),
cast(最大风速*10 as int),
cast(最大风速的风向 as int),
cast(最大风速出现时间 as char(10)),
cast(小时每分钟雨量 as char(120)),
cast(小时雨量*10 as int),
NULL,
NULL,
cast(气温*10 as int),
cast(最高气温*10 as int),
cast(最高气温出现时间 as char(10)),
cast(最低气温*10 as int),
cast(最低气温出现时间 as char(10)),
cast(相对湿度 as int),
cast(最小相对湿度 as int),
cast(最小相对湿度出现时间 as char(10)),
cast(本站气压*10 as int),
cast(最高本站气压*10 as int),
cast(最高本站气压出现时间 as char(10)),
cast(最低本站气压*10 as int),
cast(最低本站气压出现时间 as char(10))
FROM openrowset('SQLOLEDB','172.19.18.133';'sa';'sa',Meso_2009.dbo.LN_2009)
where DATEPART(second, cast(观测时间 as dateTime))=0 and @begintime<观测时间 and 观测时间<@endtime
-----------------
我想计算相同区站号,雨量的合计
下面是查询数据结果
L6525 2009-06-11 14:21:00.000 G 2009-06-11 14:20:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0 NULL NULL 199 221 06 11 2009 183 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL
L6549 2009-06-11 14:21:00.000 G 2009-06-11 14:20:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0 NULL NULL 204 212 06 11 2009 197 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL
L6573 2009-06-11 14:21:00.000 G 2009-06-11 14:20:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0 NULL NULL 198 201 06 11 2009 193 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL
L6589 2009-06-11 14:21:00.000 G 2009-06-11 14:20:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0 NULL NULL 207 217 06 11 2009 203 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL
L6669 2009-06-11 14:21:00.000 G 2009-06-11 14:20:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0 NULL NULL 207 207 06 11 2009 196 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL
L6693 2009-06-11 14:21:00.000 G 2009-06-11 14:20:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000010000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 1 NULL NULL 202 202 06 11 2009 190 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL
L6726 2009-06-11 14:21:00.000 G 2009-06-11 14:20:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0 NULL NULL 192 197 06 11 2009 186 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL
L6735 2009-06-11 14:21:00.000 G 2009-06-11 14:20:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0 NULL NULL 210 214 06 11 2009 202 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL
L6752 2009-06-11 14:21:00.000 G 2009-06-11 14:20:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0 NULL NULL 197 201 06 11 2009 196 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL
L6800 2009-06-11 14:21:00.000 G 2009-06-11 14:20:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0 NULL NULL 204 210 06 11 2009 201 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL
L6828 2009-06-11 14:20:00.000 G 2009-06-11 14:20:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0 NULL NULL 200 201 06 11 2009 196 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL
L6838 2009-06-11 14:21:00.000 G 2009-06-11 14:20:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0 NULL NULL 197 204 06 11 2009 197 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL
L6846 2009-06-11 14:20:00.000 G 2009-06-11 14:20:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0 NULL NULL 202 204 06 11 2009 192 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL
L6883 2009-06-11 14:21:00.000 G 2009-06-11 14:20:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0 NULL NULL 205 207 06 11 2009 202 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL
L6950 2009-06-11 14:21:00.000 G 2009-06-11 14:20:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0 NULL NULL 193 193 06 11 2009 190 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL
L6509 2009-06-11 14:11:00.000 G 2009-06-11 14:10:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0 NULL NULL 188 196 06 11 2009 185 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL
L6525 2009-06-11 14:11:00.000 G 2009-06-11 14:10:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0 NULL NULL 211 221 06 11 2009 183 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL
L6549 2009-06-11 14:11:00.000 G 2009-06-11 14:10:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0 NULL NULL 211 212 06 11 2009 203 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL
L6573 2009-06-11 14:11:00.000 G 2009-06-11 14:10:00.000 NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL 000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0 NULL NULL 194 196 06 11 2009 193 06 11 2009 NULL NULL NULL NULL NULL NULL NULL NULL