你试下这样是否满足你的要求,按照你所写的内容,因为有开始时间和结束时间,所以会有很多数据时间上是处于交叉的,
如开始时间满足时间要求,但是结束时间不在咱们的时间段内,这些我都给算成是不统计的数据了。
select A.BILLING,sum(out + in) from inf.EVEN_TGAS A
where (SUBSTRING(A.START_TIME,1,11) >= '2015-01-01' and SUBSTRING(A.START_TIME,1,11)<= '2015-01-31')--判断开始日期是否符合时间段
and (SUBSTRING(A.end_TIME,1,11) >= '2015-01-01' and SUBSTRING(A.end_TIME,1,11)<= '2015-01-31')--判断结束日期是否符合时间段
and (
(SUBSTRING(A.START_TIME,12,8) >= '22:00:00' and SUBSTRING(A.START_TIME,12,8)<= '23:59:59')
or
(SUBSTRING(A.START_TIME,12,8) >= '00:00:00' and SUBSTRING(A.START_TIME,12,8)<= '09:00:00')
)and (
(SUBSTRING(A.end_TIME,12,8) >= '22:00:00' and SUBSTRING(A.end_TIME,12,8)<= '23:59:59')
or
(SUBSTRING(A.end_TIME,12,8) >= '00:00:00' and SUBSTRING(A.end_TIME,12,8)<= '09:00:00')
)and (
datediff(second,A.START_TIME,A.end_TIME) <= 39600 -- 保证时间不是跨度好多天的数据
)
GROUP BY A.BILLING
limit 10;
语句比较繁杂,你可以试着优化优化