表1:st_rain_s(基本信息表)
编号stcd, 时间TM,日降雨量DYP
测试数据 1,2010-3-23 13:43:54, 1.25
2, 2010-3-22 13:44:07, 2.20表2:rain_stcd_alarmnorm(预警相关)
编号stcd,警戒雨量warnDrp,危险雨量moveDrp
测试数据 1,1, 2.00
2, 2, 3.10 -----------------要显示的数据格式--------------------
危险雨量站点数 警戒雨量站点数 无雨量站点数
昨日 2 1 0
今日 0 5 0------------------------------------------------------declare @date varchar(50)
select @date=convert(varchar(20),getdate(),23) --获取今日日期:'2010-03-23'
--print @date
select convert(varchar(20),TM,23)'日期',
count(case when datediff(day,TM,@date)=0 then stcd end)'今日',
count(case when datediff(day,TM,@date)=1 then stcd end)'昨日'
from st_rain_s where datediff(day,TM,@date)<=1
group by convert(varchar(20),TM,23) order by convert(varchar(20),TM,23) desc-------以上sql执行结果------------
日期 今日 昨日
2010-3-23 13:48:49 1 0
2010-3-22 13:49:10 2 2请问怎么在上面的语句加上和rain_stcd_alarmnorm 字段比较值。
编号stcd, 时间TM,日降雨量DYP
测试数据 1,2010-3-23 13:43:54, 1.25
2, 2010-3-22 13:44:07, 2.20表2:rain_stcd_alarmnorm(预警相关)
编号stcd,警戒雨量warnDrp,危险雨量moveDrp
测试数据 1,1, 2.00
2, 2, 3.10 -----------------要显示的数据格式--------------------
危险雨量站点数 警戒雨量站点数 无雨量站点数
昨日 2 1 0
今日 0 5 0------------------------------------------------------declare @date varchar(50)
select @date=convert(varchar(20),getdate(),23) --获取今日日期:'2010-03-23'
--print @date
select convert(varchar(20),TM,23)'日期',
count(case when datediff(day,TM,@date)=0 then stcd end)'今日',
count(case when datediff(day,TM,@date)=1 then stcd end)'昨日'
from st_rain_s where datediff(day,TM,@date)<=1
group by convert(varchar(20),TM,23) order by convert(varchar(20),TM,23) desc-------以上sql执行结果------------
日期 今日 昨日
2010-3-23 13:48:49 1 0
2010-3-22 13:49:10 2 2请问怎么在上面的语句加上和rain_stcd_alarmnorm 字段比较值。
if object_id('[st_rain_s]') is not null drop table [st_rain_s]
create table [st_rain_s]([stcd] int,[TM] datetime,[DYP] numeric(3,2))
insert [st_rain_s]
select 1,'2010-3-23 13:43:54',1.25 union all
select 2,'2010-3-22 13:44:07',2.20
--> 测试数据:[rain_stcd_alarmnorm]
if object_id('[rain_stcd_alarmnorm]') is not null drop table [rain_stcd_alarmnorm]
create table [rain_stcd_alarmnorm]([stcd] int,[warnDrp] int,[moveDrp] numeric(3,2))
insert [rain_stcd_alarmnorm]
select 1,1,2.00 union all
select 2,2,3.10select * from [st_rain_s]
select * from [rain_stcd_alarmnorm]SELECT CONVERT(VARCHAR(10),S.TM,120)AS [TM],S.[DYP],R.[warnDrp] AS [警戒雨量],R.[moveDrp] AS [危险雨量],
[状态] = CASE WHEN S.[DYP] >=R.[warnDrp] AND S.[DYP] <R.[moveDrp] THEN '警戒'
WHEN S.[DYP] >=R.[moveDrp] THEN '危险'
ELSE '正常' END
FROM [st_rain_s] S
INNER JOIN [rain_stcd_alarmnorm] R ON S.stcd = R.stcd
ORDER BY TM DESC/*
TM DYP 警戒雨量 危险雨量 状态
2010-03-23 1.25 1 2.00 警戒
2010-03-22 2.20 2 3.10 警戒
*/这样????
declare @date varchar(50)
select @date=convert(varchar(20),getdate(),23) --获取今日日期:'2010-03-23'
--print @date
select convert(varchar(20),TM,23)'数据日期'
case when datediff(day,TM,@date)=0 then
count(case when DYP>b.moveDrp then a.stcd end)'超危险雨量站点数'
count(case when DYP<b.warnDrp and DYP>moveDrp then a.stcd end)'超警戒雨量站点数',
count(case when DYP<b.warnDrp then a.stcd end)'一般降雨站点数',
count(case when DYP=0 then a.stcd end)'无雨站点数'
end
case when datediff(day,TM,@date)=1 then
count(case when DYP>b.moveDrp then a.stcd end)'超危险雨量站点数'
count(case when DYP<b.warnDrp and DYP>moveDrp then a.stcd end)'超警戒雨量站点数',
count(case when DYP<b.warnDrp then a.stcd end)'一般降雨站点数',
count(case when DYP=0 then a.stcd end)'无雨站点数'
end
from st_rain_s a,rain_stcd_alarmnorm b where datediff(day,TM,@date)<=1
group by convert(varchar(20),TM,23) order by convert(varchar(20),TM,23) desc
--
在关键字 'case' 附近有语法错误。
--if object_id('[st_rain_s]') is not null drop table [st_rain_s]
--create table [st_rain_s]([stcd] int,[TM] datetime,[DYP] numeric(3,2))
--insert [st_rain_s]
--select 1,'2010-3-23 13:43:54',1.25 union all
--select 2,'2010-3-22 13:44:07',2.20
----> 测试数据:[rain_stcd_alarmnorm]
--if object_id('[rain_stcd_alarmnorm]') is not null drop table [rain_stcd_alarmnorm]
--create table [rain_stcd_alarmnorm]([stcd] int,[warnDrp] int,[moveDrp] numeric(3,2))
--insert [rain_stcd_alarmnorm]
--select 1,1,2.00 union all
--select 2,2,3.10--select * from [st_rain_s]
--select * from [rain_stcd_alarmnorm]
declare @date varchar(50)
select @date=convert(varchar(20),getdate(),23) --获取今日日期:'2010-03-23'
--print @date
select convert(varchar(20),TM,23)'数据日期',
count(case when datediff(day,TM,@date)=0 and DYP>b.moveDrp then a.stcd end) '超危险雨量站点数',
count(case when datediff(day,TM,@date)=0 and DYP<b.warnDrp and DYP>moveDrp then a.stcd end) '超警戒雨量站点数',
count(case when datediff(day,TM,@date)=0 and DYP<b.warnDrp then a.stcd end) '一般降雨站点数',
count(case when datediff(day,TM,@date)=0 and DYP=0 then a.stcd end) '无雨站点数'
,
count(case when datediff(day,TM,@date)=1 and DYP>b.moveDrp then a.stcd end) '超危险雨量站点数' ,
count(case when datediff(day,TM,@date)=1 and DYP<b.warnDrp and DYP>moveDrp then a.stcd end) '超警戒雨量站点数',
count(case when datediff(day,TM,@date)=1 and DYP<b.warnDrp then a.stcd end) '一般降雨站点数',
count(case when datediff(day,TM,@date)=1 and DYP=0 then a.stcd end) '无雨站点数'
from st_rain_s a,rain_stcd_alarmnorm b
where datediff(day,TM,@date)<=1
group by convert(varchar(20),TM,23)
order by convert(varchar(20),TM,23) desc/*
数据日期 超危险雨量站点数 超警戒雨量站点数 一般降雨站点数 无雨站点数 超危险雨量站点数 超警戒雨量站点数 一般降雨站点数 无雨站点数
2010-03-23 0 0 1 0 0 0 0 0
2010-03-22 0 0 0 0 1 0 0 0
*/
declare @date varchar(50)
select @date=convert(varchar(20),getdate(),23) --获取今日日期:'2010-03-23'
--print @date
select convert(varchar(20),TM,23)'数据日期',
count(case when DYP>b.moveDrp then a.stcd end) '超危险雨量站点数',
count(case when DYP>b.warnDrp and DYP<moveDrp then a.stcd end) '超警戒雨量站点数',
count(case when DYP<b.warnDrp then a.stcd end) '一般降雨站点数',
count(case when DYP=0 then a.stcd end) '无雨站点数' from st_rain_s a,rain_stcd_alarmnorm b
where datediff(day,TM,@date)<=1 and a.stcd=b.stcd
group by convert(varchar(20),TM,23)
order by convert(varchar(20),TM,23) desc
//数据结果显示:数据日期 超危险雨量站点数 超警戒雨量站点数 一般降雨站点数 无雨量站点数
2010-4-1 1 1 0 0
2010-3-31 0 0 2 1