select 编号,sum(datediff(hour,开始时间,结束时间) from table1 group by 编号
/* time_s 开始时间 time_e 结束时间 codNO 编号 IDnum id号 你得把 time_e-time_s 改成 (datediff(minute,time_s,time_e)*1.0/60) 我懒得改了,菜鸟我写那吗多觉得累 没测试,应该有错,但希望对你有启发 */select codNO, --编号 toatal_time= sum(time_e-time_s)- /*计算在同一个编号下所有时间段的合*/ ( select 1 ,sum(case /*计算在同一个编号下所有时间段的重叠部分的合*/ when t.time_s>=time_s and t.time_s<=time_e and t.time_e>time_e then time_e-t.time_s /*以上两值相减得到所有未重叠时间段的合,即所需要的值*/ when t.time_e>=time_s and t.time_e<=time_e and t.time_s<time_s then t.time_e-time_s when t.time_s>=time_s and t.time_e<=time_e then t.time_e-t.time_s when time_s>=t.time_s and time_e<=t.time_e then time_e-time_s else 0) from tab where IDnum>t.IDnum and codNO=t.codNO ) from tab t group by codNO order by codNO
上面太乱鸟 /* time_s 开始时间 time_e 结束时间 codNO 编号 IDnum id号 你得把 time_e-time_s 改成 (datediff(minute,time_s,time_e)*1.0/60) 我懒得改了,菜鸟我写那吗多觉得累 没测试,应该有错,但希望对你有启发 */select codNO, --编号 toatal_time= sum(time_e-time_s)- /*计算在同一个编号下所有时间段的合*/ ( select 1 ,sum(case /*计算在同一个编号下所有时间段的重叠部分的合*/ when t.time_s>=time_s and t.time_s<=time_e and t.time_e>time_e then time_e-t.time_s when t.time_e>=time_s and t.time_e<=time_e and t.time_s<time_s then t.time_e-time_s when t.time_s>=time_s and t.time_e<=time_e then t.time_e-t.time_s when time_s>=t.time_s and time_e<=t.time_e then time_e-time_s else 0) /*以上两值相减得到所有未重叠时间段的合,即所需要的值*/ from tab where IDnum>t.IDnum and codNO=t.codNO ) from tab t group by codNO order by codNO
from table1 group by 编号
time_s 开始时间
time_e 结束时间
codNO 编号
IDnum id号
你得把 time_e-time_s 改成 (datediff(minute,time_s,time_e)*1.0/60)
我懒得改了,菜鸟我写那吗多觉得累
没测试,应该有错,但希望对你有启发
*/select
codNO, --编号
toatal_time= sum(time_e-time_s)- /*计算在同一个编号下所有时间段的合*/
(
select 1 ,sum(case /*计算在同一个编号下所有时间段的重叠部分的合*/
when t.time_s>=time_s and t.time_s<=time_e and t.time_e>time_e then time_e-t.time_s /*以上两值相减得到所有未重叠时间段的合,即所需要的值*/
when t.time_e>=time_s and t.time_e<=time_e and t.time_s<time_s then t.time_e-time_s
when t.time_s>=time_s and t.time_e<=time_e then t.time_e-t.time_s
when time_s>=t.time_s and time_e<=t.time_e then time_e-time_s
else 0)
from tab where IDnum>t.IDnum and codNO=t.codNO
)
from tab t
group by codNO
order by codNO
/*
time_s 开始时间
time_e 结束时间
codNO 编号
IDnum id号
你得把 time_e-time_s 改成 (datediff(minute,time_s,time_e)*1.0/60)
我懒得改了,菜鸟我写那吗多觉得累
没测试,应该有错,但希望对你有启发
*/select
codNO, --编号
toatal_time= sum(time_e-time_s)- /*计算在同一个编号下所有时间段的合*/
(
select 1 ,sum(case /*计算在同一个编号下所有时间段的重叠部分的合*/
when t.time_s>=time_s and t.time_s<=time_e and t.time_e>time_e then time_e-t.time_s
when t.time_e>=time_s and t.time_e<=time_e and t.time_s<time_s then t.time_e-time_s
when t.time_s>=time_s and t.time_e<=time_e then t.time_e-t.time_s
when time_s>=t.time_s and time_e<=t.time_e then time_e-time_s
else 0) /*以上两值相减得到所有未重叠时间段的合,即所需要的值*/
from tab where IDnum>t.IDnum and codNO=t.codNO
)
from tab t
group by codNO
order by codNO