莫小默
2015-12-03 02:14
采纳率: 100%
浏览 1.5k

关于数据库sql拼写!急!!!!

从2015-01-01开始到2015-01-31,提取每天22点到第二天9点的数据,注意有开始时间(START_TIME)和结束时间(END_TIME)
我的sql:
select A.BILLING,sum(out + in) from inf.EVEN_TGAS A
where A.START_TIME between '2015-01-01 22:00:00' and '2015-01-31 09:00:00'
and A.end_TIME between '2015-01-01 22:00:00' and '2015-01-31 09:00:00'
GROUP BY A.BILLING
limit 10;

怎么才能不提取比如像2015-01-02 14:23:50那个时间点的数据呢???
麻烦各位大神了

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

3条回答 默认 最新

  • 晓呆同学 2015-12-03 02:44
    已采纳

    你试下这样是否满足你的要求,按照你所写的内容,因为有开始时间和结束时间,所以会有很多数据时间上是处于交叉的,
    如开始时间满足时间要求,但是结束时间不在咱们的时间段内,这些我都给算成是不统计的数据了。

         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;
    

    语句比较繁杂,你可以试着优化优化

    点赞 打赏 评论
  • bdmh 2015-12-03 02:22

    那就别用between 了,用 > 和 <

    点赞 打赏 评论
  • 莫小默 2015-12-03 03:13

    ERROR: column "second" does not exist 这个怎么解决啊

    点赞 打赏 评论

相关推荐 更多相似问题