日期:2014-05-17 浏览次数:20450 次
select
convert(varchar(10,时间,120) as 时间,
max(case when datepart(hh,时间) between 0 and 7 then 电表读数 else 0 end)-
min(case when datepart(hh,时间) between 0 and 7 then 电表读数 else 0 end) as 谷电电量,
max(case when datepart(hh,时间) between 8 and 23 then 电表读数 else 0 end)-
min(case when datepart(hh,时间) between 8 and 23 then 电表读数 else 0 end) as 峰电电量
from
tb
group by
convert(varchar(10,时间,120)
create table #tb(时间 datetime,电表读数 int)
insert into #tb
select '2013-10-1 0:01:04',100
union all select '2013-10-1 7:59:04',169
union all select '2013-10-1 8:01:04',170
union all select '2013-10-1 23:59:04',249
union all select '2013-10-2 0:01:04',250
union all select '2013-10-2 7:59:04',449
union all select '2013-10-2 8:01:04',500
union all select '2013-10-2 23:59:04',750
select
convert(varchar(10),时间,120) as 时间,
max(case when datepart(hh,时间) between 0 and 7 then 电表读数 end)-
min(case when datepart(hh,时间) between 0 and 7 then 电表读数 end) as 谷电电量,
max(case when datepart(hh,时间) between 8 and 23 then 电表读数 end)-
min(case when datepart(hh,时间) between 8 and 23 then 电表读数 end) as 峰电电量
from #tb
group by convert(varchar(10),时间,120)
/*
时间 谷电电量 峰电电量
----------------------------------
2013-10-01 69 79
2013-10-02 199 250
*/