如下一张表:
时间 电表读数
2013-10-1 0:01:04 100
2013-10-1 7:59:04 169
2013-10-1 8:01:04 170
2013-10-1 23:59:04 249
2013-10-2 0:01:04 250
2013-10-2 7:59:04 449
2013-10-2 8:01:04 500
2013-10-2 23:59:04 750
其中0点到8点是谷电，8点到24点是峰电
想得到如下的结果
时间 谷电电量 峰电电量
2013-10-1 69 79
2013-10-2 199 250
实在想不出查询语句，请赐教
------解决方案--------------------
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
*/