我的数据库中存储的是每隔一分钟的数据,例如风向(具体的角度数)
风向可划分为8个风向(北风,东北风等等),北风的角度数为337.5~360和0~22.5度,其它风向类似
现在我想统计出一段时间内(例如一天,一个月等等),每个风向(8个风向)的频率,并取最大频率的风向
请问怎么实现呢?
谢谢
风向可划分为8个风向(北风,东北风等等),北风的角度数为337.5~360和0~22.5度,其它风向类似
现在我想统计出一段时间内(例如一天,一个月等等),每个风向(8个风向)的频率,并取最大频率的风向
请问怎么实现呢?
谢谢
西风
from tb
那我这么写怎么样
select 北风=sum(case when 角度 between 337.5~360 and 0~22.5 then 1 else 0 end)
东风=sum(case when 角度 between 67.5~112.5 then 1 else 0 end)
In(select 风向 from tb where 时间 between 开始时间 and 结束时间)
DROP PROCEDURE up_statWindDirection
GOCREATE PROCEDURE up_statWindDirection
@ldBeginNo char(20),
@ldEndNo char(20),
@N_Wind int output,
@NE_WInd int output,
@E_Wind int output,
@SE_Wind int output,
@S_Wind int output,
@SW_Wind int output,
@W_Wind int output,
@NW_Wind int output
AS
BEGIN
declare @cmd varchar(1000) select @N_Wind = sum(case when ld_wind_direction >337.5 or ld_wind_direction < 22.5 then 1 else 0 end),
@NE_Wind = sum(case when ld_wind_direction between 22.5 and 67.5 then 1 else 0 end),
@E_Wind = sum(case when ld_wind_direction between 67.5 and 112.5 then 1 else 0 end),
@SE_Wind = sum(case when ld_wind_direction between 112.5 and 157.5 then 1 else 0 end),
@S_Wind = sum(case when ld_wind_direction between 157.5 and 202.5 then 1 else 0 end),
@SW_Wind = sum(case when ld_wind_direction between 202.5 and 247.5 then 1 else 0 end),
@W_Wind = sum(case when ld_wind_direction between 247.5 and 292.5 then 1 else 0 end),
@NW_Wind = sum(case when ld_wind_direction between 292.5 and 337.5 then 1 else 0 end)
from dbo.STData where ld_wind_direction IN(select ld_wind_direction where ld_no between @ldBeginNo AND @ldEndNo)
ENDpublic void StatWindDirection(string ldBeginNo, string ldEndNo, ref int N_Wind, ref int NE_Wind,ref int E_Wind, ref int SE_Wind, ref int S_Wind, ref int SW_Wind,
ref int W_Wind, ref int NW_Wind)
{
cmd = new SqlCommand("up_statWindDirection", GetConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@ldBeginNo", SqlDbType.Char);
cmd.Parameters["@ldBeginNo"].Value = ldBeginNo;
cmd.Parameters.Add("@ldEndNo", SqlDbType.Char);
cmd.Parameters["@ldEndNo"].Value = ldBeginNo; cmd.Parameters.Add("@N_Wind", SqlDbType.Int);
cmd.Parameters["@N_Wind"].DbType = DbType.Int32;
cmd.Parameters["@N_Wind"].Direction = ParameterDirection.Output; cmd.Parameters.Add("@NE_Wind", SqlDbType.Int);
cmd.Parameters["@NE_Wind"].DbType = DbType.Int32;
cmd.Parameters["@NE_Wind"].Direction = ParameterDirection.Output; cmd.Parameters.Add("@E_Wind", SqlDbType.Int);
cmd.Parameters["@E_Wind"].DbType = DbType.Int32;
cmd.Parameters["@E_Wind"].Direction = ParameterDirection.Output; cmd.Parameters.Add("@SE_Wind", SqlDbType.Int);
cmd.Parameters["@SE_Wind"].DbType = DbType.Int32;
cmd.Parameters["@SE_Wind"].Direction = ParameterDirection.Output; cmd.Parameters.Add("@S_Wind", SqlDbType.Int);
cmd.Parameters["@S_Wind"].DbType = DbType.Int32;
cmd.Parameters["@S_Wind"].Direction = ParameterDirection.Output; cmd.Parameters.Add("@SW_Wind", SqlDbType.Int);
cmd.Parameters["@SW_Wind"].DbType = DbType.Int32;
cmd.Parameters["@SW_Wind"].Direction = ParameterDirection.Output; cmd.Parameters.Add("@W_Wind", SqlDbType.Int);
cmd.Parameters["@W_Wind"].DbType = DbType.Int32;
cmd.Parameters["@W_Wind"].Direction = ParameterDirection.Output; cmd.Parameters.Add("@NW_Wind", SqlDbType.Int);
cmd.Parameters["@NW_Wind"].DbType = DbType.Int32;
cmd.Parameters["@NW_Wind"].Direction = ParameterDirection.Output; try
{
cmd.ExecuteNonQuery();
}
catch (System.Exception e)
{
Console.WriteLine(e.ToString());
}
}上述代码分别是我写的存储过程和C#调用存储过程的代码
但是结果每个风向都是0啊
数据库里面确实有值的