我有两个表:
数据表:T_TimeData
F_Total, F_Actual, F_FileTime
208 180 2011-09-14 0:08:00
305 255 2011-09-14 0:35:00
409 360 2011-09-14 2:03:00
时间间隔表:TV_PartTime
F_Start F_End F_Name
0:00 1:59 2:00
2:00 3:59 4:00
4:00 5:59 6:00
6:00 7:59 8:00
....需要按时间间隔表分类汇总数据表中的数据[只会取一天的数据],如果这个时间段没有数据则显示0
类似结果如下:
时间段名 F_Total F_Actual
2:00 513 435
4:00 409 360
6:00 0 0谢谢
数据表:T_TimeData
F_Total, F_Actual, F_FileTime
208 180 2011-09-14 0:08:00
305 255 2011-09-14 0:35:00
409 360 2011-09-14 2:03:00
时间间隔表:TV_PartTime
F_Start F_End F_Name
0:00 1:59 2:00
2:00 3:59 4:00
4:00 5:59 6:00
6:00 7:59 8:00
....需要按时间间隔表分类汇总数据表中的数据[只会取一天的数据],如果这个时间段没有数据则显示0
类似结果如下:
时间段名 F_Total F_Actual
2:00 513 435
4:00 409 360
6:00 0 0谢谢
from TV_PartTime b left join T_TimeData a
on a.F_FileTime between convert(varchar(10),a.F_FileTime between,120) + ' ' + b.F_Start and convert(varchar(10),a.F_FileTime between,120) + ' ' + b.F_End
group by b.F_Name
select b.F_name,a.f_total,a.f_actual
from t_timedata a inner join tv_parttime b on a.f_filetime between b.f_ftart and b.f_end
)t group by f_name
select b.F_name,a.f_total,a.f_actual
from t_timedata a inner join tv_parttime b on convert(varchar(5),a.f_filetime,108) between b.f_ftart and b.f_end
)t group by f_name
--> 测试数据: [T_TimeData]
if object_id('[T_TimeData]') is not null drop table [T_TimeData]
create table [T_TimeData] (F_Total int,F_Actual int,F_FileTime datetime)
insert into [T_TimeData]
select 208,180,'2011-09-14 0:08:00' union all
select 305,255,'2011-09-14 0:35:00' union all
select 409,360,'2011-09-14 2:03:00'
--> 测试数据: [TV_PartTime]
if object_id('[TV_PartTime]') is not null drop table [TV_PartTime]
create table [TV_PartTime] (F_Start datetime,F_End datetime,F_Name datetime)
insert into [TV_PartTime]
select '0:00','1:59','2:00' union all
select '2:00','3:59','4:00' union all
select '4:00','5:59','6:00' union all
select '6:00','7:59','8:00'select 时间段名=convert(varchar(5),f_name,108),
F_Total=isnull(sum(f_total),0),
F_Actual=isnull(sum(f_actual),0)
from(
select b.F_name,a.f_total,a.f_actual
from t_timedata a right join tv_parttime b
on convert(varchar(5),a.f_filetime,108) between b.f_start and b.f_end
)t
group by f_namedrop table [T_TimeData],[TV_PartTime]/*
时间段名 F_Total F_Actual
----- ----------- -----------
02:00 513 435
04:00 409 360
06:00 0 0
08:00 0 0
insert into t_timedata select 208,180,'2011-09-14 0:08:00'
insert into t_timedata select 305,255,'2011-09-14 0:35:00'
insert into t_timedata select 409,360,'2011-09-14 2:03:00'
create table TV_PartTime(F_Start varchar(10),F_End varchar(10),F_Name varchar(10))
insert into tv_parttime select '0:00','1:59','2:00'
insert into tv_parttime select '2:00','3:59','4:00'
insert into tv_parttime select '4:00','5:59','6:00'
insert into tv_parttime select '6:00','7:59','8:00'
go
select f_name,sum(f_total)F_Total,sum(f_actual) F_Actual from(
select b.F_name,a.f_total,a.f_actual
from t_timedata a inner join tv_parttime b on convert(varchar(5),a.f_filetime,108) between convert(varchar(5),convert(datetime,b.f_start),108) and convert(varchar(5),convert(datetime,b.f_end),108)
)t group by f_name
/*
f_name F_Total F_Actual
---------- ----------- -----------
2:00 513 435
4:00 409 360(2 行受影响)*/
go
drop table T_TimeData,tv_parttime
把第二个表改成这样:
insert into tv_parttime select '00:00','01:59','2:00'
insert into tv_parttime select '02:00','03:59','4:00'
insert into tv_parttime select '04:00','05:59','6:00'
insert into tv_parttime select '06:00','07:59','8:00'会省去很多麻烦.