以下是一段示意的语句,希望你能看明白: select case when coldate>=@startdate and coldate<dateadd(day,5,@startdate) then 1 when coldate>=dateadd(day,5,@startdate) and coldate<dateadd(day,10,@startdate) then 2 ... when coldate>=dateadd(day,25,@startdate) and coldate<@Enddate then 6 end as type,colnumber into #Temp from TableName where coldate<@Enddate and coldate>=@Startdate select type,avg(colnumber) from #Temp group by type
select FLOOR(DATEDIFF(day, @startdate, coldate)/(DATEDIFF(day, @startdate, @enddate)/n)) as segment,* into #Temp from TableName where coldate<@Enddate and coldate>=@Startdate 平均值: select type,avg(colnumber) from #Temp group by segment“最后一条记录”:在关系型数据库中没有这个概念。你的意思是不是在每一段里coldate最大的记录?那么: sellectt * from #temp t1 where coldate=(select max(coldate) from #temp t2 where t1.segment=t2.segment)
最后一条语句错了,应该是: select * from #temp t1 where coldate=(select max(coldate) from #temp t2 where t1.segment=t2.segment)
select case when coldate>=@startdate and coldate<dateadd(day,5,@startdate) then 1
when coldate>=dateadd(day,5,@startdate) and coldate<dateadd(day,10,@startdate) then 2
...
when coldate>=dateadd(day,25,@startdate) and coldate<@Enddate then 6 end as type,colnumber
into #Temp
from TableName
where coldate<@Enddate
and coldate>=@Startdate select type,avg(colnumber) from #Temp
group by type
假设表名是tablename,时间范围是从@startdate到@enddate,相关字段
是coldate,要求平均分为n段,那么:
select FLOOR(DATEDIFF(day, @startdate, coldate)/(DATEDIFF(day, @startdate, @enddate)/n)) as segment,*
into #Temp
from TableName
where coldate<@Enddate
and coldate>=@Startdate 平均值:
select type,avg(colnumber) from #Temp
group by segment“最后一条记录”:在关系型数据库中没有这个概念。你的意思是不是在每一段里coldate最大的记录?那么:
sellectt *
from #temp t1
where coldate=(select max(coldate) from #temp t2 where t1.segment=t2.segment)
select *
from #temp t1
where coldate=(select max(coldate) from #temp t2 where t1.segment=t2.segment)