查询每天晚上19点到第二天早上7点的数据

qq_38446992 2017-09-25 04:20:50
源数据如下：
CollectTime liuliang
2017-09-01 07:01:06 1172.936
2017-09-01 08:02:47 1172.981
2017-09-01 09:04:29 1173.023
2017-09-01 10:06:10 1173.063
2017-09-01 12:07:52 1173.11
2017-09-01 15:09:33 1173.156
2017-09-01 16:15:20 1174.42
2017-09-01 17:15:20 1174.46
2017-09-01 18:15:20 1174.503
2017-09-01 19:00:20 1174.542
2017-09-01 19:02:18 1174.545
2017-09-01 19:05:59 1174.548
2017-09-01 19:09:20 1174.551
2017-09-01 20:15:20 1174.585
2017-09-01 21:18:22 1174.629
2017-09-02 00:18:22 1174.673
2017-09-02 01:18:05 1174.715
2017-09-02 02:15:02 1174.752
2017-09-02 03:18:05 1174.792
2017-09-02 04:00:01 1174.832
2017-09-02 06:18:09 1174.873
2017-09-02 06:58:09 1174.893
2017-09-02 07:00:22 1174.913
2017-09-02 07:01:22 1174.923
2017-09-02 07:18:22 1174.953
2017-09-02 09:33:01 1177.265
2017-09-02 11:00:25 1177.306
2017-09-02 13:18:22 1177.347
2017-09-02 16:18:55 1177.388
2017-09-02 18:00:22 1177.428
2017-09-02 19:00:55 1177.467
2017-09-02 19:01:01 1177.487
2017-09-02 19:03:55 1177.497
2017-09-02 19:30:55 1177.499
2017-09-02 21:18:22 1177.508
2017-09-02 23:18:59 1177.55
2017-09-03 01:07:22 1177.586
2017-09-03 02:01:18 1177.626
2017-09-03 03:07:22 1177.667
2017-09-03 05:07:55 1177.708
2017-09-03 06:07:22 1177.749
2017-09-03 06:58:22 1177.759
2017-09-03 07:00:33 1177.788
查询每天晚上19点到第二天早上7点的流量数据，流量数据=第二天早上7点的流量减去每天晚上19点的流量，sql如何写，麻烦哪位大神帮帮忙，想得到结果表如下所示：
CollectTime liuliang
2017-09-01 0.371
2017-09-02 0.321

...全文
1151 8 打赏 收藏 转发到动态 举报
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
zbdzjx 2017-09-26
  • 打赏
  • 举报
 回复 
with t1(dt, ll) as
(
select '2017-09-01 07:01:06',1172.936 union all
select '2017-09-01 08:02:47',1172.981 union all
select '2017-09-01 09:04:29',1173.023 union all
select '2017-09-01 10:06:10',1173.063 union all
select '2017-09-01 12:07:52',1173.11 union all
select '2017-09-01 15:09:33',1173.156 union all
select '2017-09-01 16:15:20',1174.42 union all
select '2017-09-01 17:15:20',1174.46 union all
select '2017-09-01 18:15:20',1174.503 union all
select '2017-09-01 19:00:20',1174.542 union all
select '2017-09-01 19:02:18',1174.545 union all
select '2017-09-01 19:05:59',1174.548 union all
select '2017-09-01 19:09:20',1174.551 union all
select '2017-09-01 20:15:20',1174.585 union all
select '2017-09-01 21:18:22',1174.629 union all
select '2017-09-02 00:18:22',1174.673 union all
select '2017-09-02 01:18:05',1174.715 union all
select '2017-09-02 02:15:02',1174.752 union all
select '2017-09-02 03:18:05',1174.792 union all
select '2017-09-02 04:00:01',1174.832 union all
select '2017-09-02 06:18:09',1174.873 union all
select '2017-09-02 06:58:09',1174.893 union all
select '2017-09-02 07:00:22',1174.913 union all
select '2017-09-02 07:01:22',1174.923 union all
select '2017-09-02 07:18:22',1174.953 union all
select '2017-09-02 09:33:01',1177.265 union all
select '2017-09-02 11:00:25',1177.306 union all
select '2017-09-02 13:18:22',1177.347 union all
select '2017-09-02 16:18:55',1177.388 union all
select '2017-09-02 18:00:22',1177.428 union all
select '2017-09-02 19:00:55',1177.467 union all
select '2017-09-02 19:01:01',1177.487 union all
select '2017-09-02 19:03:55',1177.497 union all
select '2017-09-02 19:30:55',1177.499 union all
select '2017-09-02 21:18:22',1177.508 union all
select '2017-09-02 23:18:59',1177.55 union all
select '2017-09-03 01:07:22',1177.586 union all
select '2017-09-03 02:01:18',1177.626 union all
select '2017-09-03 03:07:22',1177.667 union all
select '2017-09-03 05:07:55',1177.708 union all
select '2017-09-03 06:07:22',1177.749 union all
select '2017-09-03 06:58:22',1177.759 union all
select '2017-09-03 07:00:33',1177.788
)
, t2(dt2) as
(
select distinct CONVERT(varchar(10), dt, 120) from t1
)
select t2.dt2, MAX(ll)-MIN(ll) from t1 
inner join t2 on t1.dt between CONVERT(datetime, t2.dt2+' 19:00:00') and CONVERT(datetime, t2.dt2+' 19:00:00')+0.5
group by t2.dt2
二月十六 版主 2017-09-26
  • 打赏
  • 举报
 回复 
--测试数据

if not object_id(N'Tempdb..#T') is null

    drop table #T

Go

 CREATE TABLE #T(CollectTime DATETIME,liuliang DECIMAL(18,5) )

Insert #T

select '2017-09-01 07:01:06',1172.936 union all

select '2017-09-01 08:02:47',1172.981 union all

select '2017-09-01 09:04:29',1173.023 union all

select '2017-09-01 10:06:10',1173.063 union all

select '2017-09-01 12:07:52',1173.11 union all

select '2017-09-01 15:09:33',1173.156 union all

select '2017-09-01 16:15:20',1174.42 union all

select '2017-09-01 17:15:20',1174.46 union all

select '2017-09-01 18:15:20',1174.503 union all

select '2017-09-01 19:00:20',1174.542 union all

select '2017-09-01 19:02:18',1174.545 union all

select '2017-09-01 19:05:59',1174.548 union all

select '2017-09-01 19:09:20',1174.551 union all

select '2017-09-01 20:15:20',1174.585 union all

select '2017-09-01 21:18:22',1174.629 union all

select '2017-09-02 00:18:22',1174.673 union all

select '2017-09-02 01:18:05',1174.715 union all

select '2017-09-02 02:15:02',1174.752 union all

select '2017-09-02 03:18:05',1174.792 union all

select '2017-09-02 04:00:01',1174.832 union all

select '2017-09-02 06:18:09',1174.873 union all

select '2017-09-02 06:58:09',1174.893 union all

select '2017-09-02 07:00:22',1174.913 union all

select '2017-09-02 07:01:22',1174.923 union all

select '2017-09-02 07:18:22',1174.953 union all

select '2017-09-02 09:33:01',1177.265 union all

select '2017-09-02 11:00:25',1177.306 union all

select '2017-09-02 13:18:22',1177.347 union all

select '2017-09-02 16:18:55',1177.388 union all

select '2017-09-02 18:00:22',1177.428 union all

select '2017-09-02 19:00:55',1177.467 union all

select '2017-09-02 19:01:01',1177.487 union all

select '2017-09-02 19:03:55',1177.497 union all

select '2017-09-02 19:30:55',1177.499 union all

select '2017-09-02 21:18:22',1177.508 union all

select '2017-09-02 23:18:59',1177.55 union all

select '2017-09-03 01:07:22',1177.586 union all

select '2017-09-03 02:01:18',1177.626 union all

select '2017-09-03 03:07:22',1177.667 union all

select '2017-09-03 05:07:55',1177.708 union all

select '2017-09-03 06:07:22',1177.749 union all

select '2017-09-03 06:58:22',1177.759 union all

select '2017-09-03 07:00:33',1177.788

Go

--测试数据结束

;WITH cte AS (

SELECT  CONVERT(VARCHAR(10), a.CollectTime, 23) AS CollectTime ,

        MIN(b.CollectTime) AS bCollectTime ,

        MIN(a.CollectTime) AS aCollectTime

FROM    #T a

        LEFT JOIN #T b ON DATEDIFF(DAY, a.CollectTime, b.CollectTime) = 1

WHERE   DATEPART(hh, a.CollectTime) = 19

        AND DATEPART(hh, b.CollectTime) = 7

        AND DATEDIFF(MINUTE, a.CollectTime, b.CollectTime) = 12 * 60

GROUP BY CONVERT(VARCHAR(10), a.CollectTime, 23)

)

SELECT  cte.CollectTime ,

        a.liuliang-b.liuliang  AS liuliang

FROM    cte

        JOIN #T a ON a.CollectTime = cte.bCollectTime

        JOIN #T b ON cte.aCollectTime = b.CollectTime


qq_38446992 2017-09-26
  • 打赏
  • 举报
 回复
对 19点最小的和7点最小的。
二月十六 版主 2017-09-26
  • 打赏
  • 举报
 回复
引用 5 楼 qq_38446992 的回复:
19点和7点，分和秒不会为0的。就是几乎每分钟都有 2017-09-18 07:01:01 2017-09-18 07:02:42 2017-09-18 07:04:23 2017-09-18 07:07:46. 2017-09-18 07:09:28 2017-09-18 07:11:09 2017-09-18 07:12:51 2017-09-18 07:14:32 2017-09-18 07:16:13 2017-09-18 07:17:54 2017-09-18 07:19:36 2017-09-18 07:21:17 2017-09-18 07:22:58 2017-09-18 07:24:39
那按照那个时间算呢？19点和7点最小的？
qq_38446992 2017-09-26
  • 打赏
  • 举报
 回复
19点和7点，分和秒不会为0的。就是几乎每分钟都有 2017-09-18 07:01:01 2017-09-18 07:02:42 2017-09-18 07:04:23 2017-09-18 07:07:46. 2017-09-18 07:09:28 2017-09-18 07:11:09 2017-09-18 07:12:51 2017-09-18 07:14:32 2017-09-18 07:16:13 2017-09-18 07:17:54 2017-09-18 07:19:36 2017-09-18 07:21:17 2017-09-18 07:22:58 2017-09-18 07:24:39
二月十六 版主 2017-09-25
  • 打赏
  • 举报
 回复
引用 2 楼 qq_38446992 的回复:
不知道为啥数据量一多结果不是我要的那种，原始数据时间就是24小时每分钟都会有数据的
试试这样 
SELECT  CONVERT(VARCHAR(10),a.CollectTime,23) AS CollectTime ,
        b.liuliang - a.liuliang AS liuliang
FROM    #T a
        LEFT JOIN #T b ON DATEDIFF(DAY, a.CollectTime, b.CollectTime) = 1
WHERE   DATEPART(hh, a.CollectTime) = 19
        AND DATEPART(hh, b.CollectTime) = 7
		AND DATEPART(MINUTE, a.CollectTime)=0
		AND DATEPART(MINUTE, b.CollectTime)=0
        AND DATEDIFF(MINUTE, a.CollectTime, b.CollectTime) = 12 * 60
楼主说的19点和7点，分和秒都会是0吗？
qq_38446992 2017-09-25
  • 打赏
  • 举报
 回复
不知道为啥数据量一多结果不是我要的那种，原始数据时间就是24小时每分钟都会有数据的
二月十六 版主 2017-09-25
  • 打赏
  • 举报
 回复
刚才写的那个有问题吗？时间问题？
--测试数据

if not object_id(N'Tempdb..#T') is null

	drop table #T

Go

 CREATE TABLE #T(CollectTime DATETIME,liuliang DECIMAL(18,5) )

Insert #T

select '2017-09-01 07:01:06',1172.936 union all

select '2017-09-01 08:02:47',1172.981 union all

select '2017-09-01 09:04:29',1173.023 union all

select '2017-09-01 10:06:10',1173.063 union all

select '2017-09-01 12:07:52',1173.11 union all

select '2017-09-01 15:09:33',1173.156 union all

select '2017-09-01 16:15:20',1174.42 union all

select '2017-09-01 17:15:20',1174.46 union all

select '2017-09-01 18:15:20',1174.503 union all

select '2017-09-01 19:00:20',1174.542 union all

select '2017-09-01 19:02:18',1174.545 union all

select '2017-09-01 19:05:59',1174.548 union all

select '2017-09-01 19:09:20',1174.551 union all

select '2017-09-01 20:15:20',1174.585 union all

select '2017-09-01 21:18:22',1174.629 union all

select '2017-09-02 00:18:22',1174.673 union all

select '2017-09-02 01:18:05',1174.715 union all

select '2017-09-02 02:15:02',1174.752 union all

select '2017-09-02 03:18:05',1174.792 union all

select '2017-09-02 04:00:01',1174.832 union all

select '2017-09-02 06:18:09',1174.873 union all

select '2017-09-02 06:58:09',1174.893 union all

select '2017-09-02 07:00:22',1174.913 union all

select '2017-09-02 07:01:22',1174.923 union all

select '2017-09-02 07:18:22',1174.953 union all

select '2017-09-02 09:33:01',1177.265 union all

select '2017-09-02 11:00:25',1177.306 union all

select '2017-09-02 13:18:22',1177.347 union all

select '2017-09-02 16:18:55',1177.388 union all

select '2017-09-02 18:00:22',1177.428 union all

select '2017-09-02 19:00:55',1177.467 union all

select '2017-09-02 19:01:01',1177.487 union all

select '2017-09-02 19:03:55',1177.497 union all

select '2017-09-02 19:30:55',1177.499 union all

select '2017-09-02 21:18:22',1177.508 union all

select '2017-09-02 23:18:59',1177.55 union all

select '2017-09-03 01:07:22',1177.586 union all

select '2017-09-03 02:01:18',1177.626 union all

select '2017-09-03 03:07:22',1177.667 union all

select '2017-09-03 05:07:55',1177.708 union all

select '2017-09-03 06:07:22',1177.749 union all

select '2017-09-03 06:58:22',1177.759 union all

select '2017-09-03 07:00:33',1177.788

Go

--测试数据结束

SELECT  CONVERT(VARCHAR(10),a.CollectTime,23) AS CollectTime ,

        b.liuliang - a.liuliang AS liuliang

FROM    #T a

        LEFT JOIN #T b ON DATEDIFF(DAY, a.CollectTime, b.CollectTime) = 1

WHERE   DATEPART(hh, a.CollectTime) = 19

        AND DATEPART(hh, b.CollectTime) = 7

        AND DATEDIFF(MINUTE, a.CollectTime, b.CollectTime) = 12 * 60


oracle实现动态查询前一天早八点到当天早八点的数据功能示例
本文实例讲述了oracle实现动态查询前一天早八点到当天早八点的数据。分享给大家供大家参考，具体如下： 需要查询前一天早八点到当天早八点的数据 这里是查询sql语句 SELECT DEPT_ID, COUNT( * ) DID FROM MES_...
SQL Server动态查询每天八点到第二天八点的数据
大于如下时间 select (case when DateName(hour,GETDATE()) < 8 and datepart(dd,GETDATE()) = 1 then CONVERT(varchar(10),dateadd(dd,-1,GETDATE()),120) else CONVERT(varchar(10),GETDATE(),120) ...
Oracle 求一个月内每天 22:00 ~ 第二天早上06:00 之间的数据
求出 Oracle 数据库表中一个月晚上22:00 ~ 第二天早上06:00 之间的数据量。 二、解决办法 1、把时间改成标准格式，使用 to_date() 函数； 2、所有时间减去 6 小时，统一到同一天； 3、之后取到小时单位即可，...
mysql 查询每天某个时间段的数据
mysql 查询每天某个时间段的数据...早上7点到九点 早高峰时间段查询 SELECT * FROM table WHERE hour(时间字段) BETWEEN 07 and 09 但是遇到一个问题就是要查凌晨的数据就不行了 因为凌晨是当天到第二天 所以: 凌...
mysql 查询每天某个时间段的数据及最接近的记录数据
早上7点到九点 早高峰时间段查询 SELECT * FROM table WHERE hour(时间字段) BETWEEN 07 and 09 但是遇到一个问题就是要查凌晨的数据就不行了 因为凌晨是当天到第二天 所以: 凌晨数据查询 SELECT * FROM ...
MS-SQL Server

34,587

社区成员

254,588

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章