取表中每个一小时的记录

cqzjb 2018-01-24 11:05:26
表数据
id 时间 值
1 2018-1-1 20:03:04 5
2 2018-1-1 20:03:34 7
2 2018-1-1 20:04:04 17
2 2018-1-1 20:04:34 6
2 2018-1-1 20:05:04 9
2 2018-1-1 20:06:34 1
以此类推
数据是每30秒有一条
想要达到的效果是
1 2018-1-1 20:03:04 5
32 2018-1-1 21:03:04 11
73 2018-1-1 22:03:04 7
以此类推
如果没有 2018-1-1 21:03:04 11这条数据，那么久取这个时间最近的数据作为2018-1-1 21:03:04 的数据
不知能否实现
...全文
1137 8 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
cqzjb 2018-03-07
  • 打赏
  • 举报
 回复
引用 3 楼 qq_37170555 的回复:
[quote=引用 2 楼 cqzjb 的回复:] [quote=引用 1 楼 qq_37170555 的回复:] 完全没看懂你要的效果怎么来的
不好意思，每表达清楚，意思是程序每个30秒采集了一条数据并记录到数据库，现在需要查询这个数据，但是不需要看每30秒的数据，而且需要看每1个小时的那一条数据即可。 不知是否表达清楚 望赐教。[/quote] 

use Tempdb
go
--> --> 听雨停了-->生成测试数据
 
if not object_id(N'Tempdb..#tab') is null
	drop table #tab
Go
Create table #tab([id] int,[时间] Datetime,[值] int)
 
Insert #tab
select 1,'2018-1-1 20:03:04',5 union all
select 2,'2018-1-1 20:03:34',7 union all
select 2,'2018-1-1 20:04:04',17 union all
select 2,'2018-1-1 20:04:34',6 union all
select 2,'2018-1-1 20:05:04',9 union all
select 2,'2018-1-1 20:06:34',1 UNION ALL
select 32,'2018-1-1 21:03:04',11 UNION ALL
select 33,'2018-1-1 21:03:34',12 UNION ALL
select 73,'2018-1-1 22:03:08',7 UNION ALL
select 74,'2018-1-1 22:03:38',8 
GO
--测试数据结束

;WITH cte AS (
	--找到整个表中最小的时间，从这个时间开始算起
	Select MIN(时间) as mintime from #tab	
),
cte2 AS (
	--每个时间减去最小时间得到的秒数
	SELECT *,CAST(时间 as date) as dt,datepart(hour,时间) as hr,datediff(second,mintime,时间) as sec
	FROM #tab,cte
),
cte3 AS (
	--按日期，小时分组得到每个日期，小时中最小的秒数
	SELECT dt,hr,MIN(sec) AS minsec FROM cte2
	GROUP BY dt,hr	
)
--两表连接得到你要的数据
SELECT a.id,a.时间,a.值 
FROM cte2 a
INNER JOIN cte3 b ON a.sec=b.minsec AND a.dt=b.dt AND a.hr=b.hr
大致是这样了[/quote] --------------- 你好，需求发生了一点点变化，就是不用每隔一小时采样了，而是每10分钟就采样一次，怎么改改cte2呢
cqzjb 2018-03-01
  • 打赏
  • 举报
 回复
引用 6 楼 qq_37170555 的回复:
[quote=引用 5 楼 cqzjb的回复:][quote=引用 4 楼 qq_37170555 的回复:] [quote=引用 3 楼 qq_37170555 的回复:] [quote=引用 2 楼 cqzjb 的回复:] [quote=引用 1 楼 qq_37170555 的回复:] 完全没看懂你要的效果怎么来的
不好意思，每表达清楚，意思是程序每个30秒采集了一条数据并记录到数据库，现在需要查询这个数据，但是不需要看每30秒的数据，而且需要看每1个小时的那一条数据即可。 不知是否表达清楚 望赐教。[/quote] 

use Tempdb
go
--> --> 听雨停了-->生成测试数据
 
if not object_id(N'Tempdb..#tab') is null
	drop table #tab
Go
Create table #tab([id] int,[时间] Datetime,[值] int)
 
Insert #tab
select 1,'2018-1-1 20:03:04',5 union all
select 2,'2018-1-1 20:03:34',7 union all
select 2,'2018-1-1 20:04:04',17 union all
select 2,'2018-1-1 20:04:34',6 union all
select 2,'2018-1-1 20:05:04',9 union all
select 2,'2018-1-1 20:06:34',1 UNION ALL
select 32,'2018-1-1 21:03:04',11 UNION ALL
select 33,'2018-1-1 21:03:34',12 UNION ALL
select 73,'2018-1-1 22:03:08',7 UNION ALL
select 74,'2018-1-1 22:03:38',8 
GO
--测试数据结束

;WITH cte AS (
	--找到整个表中最小的时间，从这个时间开始算起
	Select MIN(时间) as mintime from #tab	
),
cte2 AS (
	--每个时间减去最小时间得到的秒数
	SELECT *,CAST(时间 as date) as dt,datepart(hour,时间) as hr,datediff(second,mintime,时间) as sec
	FROM #tab,cte
),
cte3 AS (
	--按日期，小时分组得到每个日期，小时中最小的秒数
	SELECT dt,hr,MIN(sec) AS minsec FROM cte2
	GROUP BY dt,hr	
)
--两表连接得到你要的数据
SELECT a.id,a.时间,a.值 
FROM cte2 a
INNER JOIN cte3 b ON a.sec=b.minsec AND a.dt=b.dt AND a.hr=b.hr
大致是这样了[/quote] 以上是sql server的，没注意你发的是ACCESS版，ACCESS应该是用不了。但是逻辑大概就是上面那样[/quote] access每关系，我自己调整一下应该没问题，谢谢，但是还有个问题是如果没有'2018-1-1 21:03:04',11这条记录，而是有一条临近时间的如'2018-1-1 21:03:11',12，能不能取出呢？[/quote] 可以，以上代码有这个功能[/quote] 谢谢，已经修改为access的sql了，就是想要的查询结果，但是access里面没有临时表和存储过程之类的，只能编写成查询，但是用程序调用这个查询的时候，速度上有点差强人意，不知道access里面的查询能不能添加索引呢？ 特别是创建下面这个查询的时候，速度有些忙，程序要进入等待状态 cte3 AS ( --按日期，小时分组得到每个日期，小时中最小的秒数 SELECT dt,hr,MIN(sec) AS minsec FROM cte2 GROUP BY dt,hr 大神们有没有什么变法，或者是有没有简单高效一点的单机数据库介绍来用用。
听雨停了 2018-01-28
  • 打赏
  • 举报
 回复
引用 5 楼 cqzjb的回复:
[quote=引用 4 楼 qq_37170555 的回复:] [quote=引用 3 楼 qq_37170555 的回复:] [quote=引用 2 楼 cqzjb 的回复:] [quote=引用 1 楼 qq_37170555 的回复:] 完全没看懂你要的效果怎么来的
不好意思，每表达清楚，意思是程序每个30秒采集了一条数据并记录到数据库，现在需要查询这个数据，但是不需要看每30秒的数据，而且需要看每1个小时的那一条数据即可。 不知是否表达清楚 望赐教。[/quote] 

use Tempdb
go
--> --> 听雨停了-->生成测试数据
 
if not object_id(N'Tempdb..#tab') is null
	drop table #tab
Go
Create table #tab([id] int,[时间] Datetime,[值] int)
 
Insert #tab
select 1,'2018-1-1 20:03:04',5 union all
select 2,'2018-1-1 20:03:34',7 union all
select 2,'2018-1-1 20:04:04',17 union all
select 2,'2018-1-1 20:04:34',6 union all
select 2,'2018-1-1 20:05:04',9 union all
select 2,'2018-1-1 20:06:34',1 UNION ALL
select 32,'2018-1-1 21:03:04',11 UNION ALL
select 33,'2018-1-1 21:03:34',12 UNION ALL
select 73,'2018-1-1 22:03:08',7 UNION ALL
select 74,'2018-1-1 22:03:38',8 
GO
--测试数据结束

;WITH cte AS (
	--找到整个表中最小的时间，从这个时间开始算起
	Select MIN(时间) as mintime from #tab	
),
cte2 AS (
	--每个时间减去最小时间得到的秒数
	SELECT *,CAST(时间 as date) as dt,datepart(hour,时间) as hr,datediff(second,mintime,时间) as sec
	FROM #tab,cte
),
cte3 AS (
	--按日期，小时分组得到每个日期，小时中最小的秒数
	SELECT dt,hr,MIN(sec) AS minsec FROM cte2
	GROUP BY dt,hr	
)
--两表连接得到你要的数据
SELECT a.id,a.时间,a.值 
FROM cte2 a
INNER JOIN cte3 b ON a.sec=b.minsec AND a.dt=b.dt AND a.hr=b.hr
大致是这样了[/quote] 以上是sql server的，没注意你发的是ACCESS版，ACCESS应该是用不了。但是逻辑大概就是上面那样[/quote] access每关系，我自己调整一下应该没问题，谢谢，但是还有个问题是如果没有'2018-1-1 21:03:04',11这条记录，而是有一条临近时间的如'2018-1-1 21:03:11',12，能不能取出呢？[/quote] 可以，以上代码有这个功能
cqzjb 2018-01-26
  • 打赏
  • 举报
 回复
引用 4 楼 qq_37170555 的回复:
[quote=引用 3 楼 qq_37170555 的回复:] [quote=引用 2 楼 cqzjb 的回复:] [quote=引用 1 楼 qq_37170555 的回复:] 完全没看懂你要的效果怎么来的
不好意思，每表达清楚，意思是程序每个30秒采集了一条数据并记录到数据库，现在需要查询这个数据，但是不需要看每30秒的数据，而且需要看每1个小时的那一条数据即可。 不知是否表达清楚 望赐教。[/quote] 

use Tempdb
go
--> --> 听雨停了-->生成测试数据
 
if not object_id(N'Tempdb..#tab') is null
	drop table #tab
Go
Create table #tab([id] int,[时间] Datetime,[值] int)
 
Insert #tab
select 1,'2018-1-1 20:03:04',5 union all
select 2,'2018-1-1 20:03:34',7 union all
select 2,'2018-1-1 20:04:04',17 union all
select 2,'2018-1-1 20:04:34',6 union all
select 2,'2018-1-1 20:05:04',9 union all
select 2,'2018-1-1 20:06:34',1 UNION ALL
select 32,'2018-1-1 21:03:04',11 UNION ALL
select 33,'2018-1-1 21:03:34',12 UNION ALL
select 73,'2018-1-1 22:03:08',7 UNION ALL
select 74,'2018-1-1 22:03:38',8 
GO
--测试数据结束

;WITH cte AS (
	--找到整个表中最小的时间，从这个时间开始算起
	Select MIN(时间) as mintime from #tab	
),
cte2 AS (
	--每个时间减去最小时间得到的秒数
	SELECT *,CAST(时间 as date) as dt,datepart(hour,时间) as hr,datediff(second,mintime,时间) as sec
	FROM #tab,cte
),
cte3 AS (
	--按日期，小时分组得到每个日期，小时中最小的秒数
	SELECT dt,hr,MIN(sec) AS minsec FROM cte2
	GROUP BY dt,hr	
)
--两表连接得到你要的数据
SELECT a.id,a.时间,a.值 
FROM cte2 a
INNER JOIN cte3 b ON a.sec=b.minsec AND a.dt=b.dt AND a.hr=b.hr
大致是这样了[/quote] 以上是sql server的，没注意你发的是ACCESS版，ACCESS应该是用不了。但是逻辑大概就是上面那样[/quote] access每关系，我自己调整一下应该没问题，谢谢，但是还有个问题是如果没有'2018-1-1 21:03:04',11这条记录，而是有一条临近时间的如'2018-1-1 21:03:11',12，能不能取出呢？
听雨停了 2018-01-26
  • 打赏
  • 举报
 回复
引用 3 楼 qq_37170555 的回复:
[quote=引用 2 楼 cqzjb 的回复:] [quote=引用 1 楼 qq_37170555 的回复:] 完全没看懂你要的效果怎么来的
不好意思，每表达清楚，意思是程序每个30秒采集了一条数据并记录到数据库，现在需要查询这个数据，但是不需要看每30秒的数据，而且需要看每1个小时的那一条数据即可。 不知是否表达清楚 望赐教。[/quote] 

use Tempdb
go
--> --> 听雨停了-->生成测试数据
 
if not object_id(N'Tempdb..#tab') is null
	drop table #tab
Go
Create table #tab([id] int,[时间] Datetime,[值] int)
 
Insert #tab
select 1,'2018-1-1 20:03:04',5 union all
select 2,'2018-1-1 20:03:34',7 union all
select 2,'2018-1-1 20:04:04',17 union all
select 2,'2018-1-1 20:04:34',6 union all
select 2,'2018-1-1 20:05:04',9 union all
select 2,'2018-1-1 20:06:34',1 UNION ALL
select 32,'2018-1-1 21:03:04',11 UNION ALL
select 33,'2018-1-1 21:03:34',12 UNION ALL
select 73,'2018-1-1 22:03:08',7 UNION ALL
select 74,'2018-1-1 22:03:38',8 
GO
--测试数据结束

;WITH cte AS (
	--找到整个表中最小的时间，从这个时间开始算起
	Select MIN(时间) as mintime from #tab	
),
cte2 AS (
	--每个时间减去最小时间得到的秒数
	SELECT *,CAST(时间 as date) as dt,datepart(hour,时间) as hr,datediff(second,mintime,时间) as sec
	FROM #tab,cte
),
cte3 AS (
	--按日期，小时分组得到每个日期，小时中最小的秒数
	SELECT dt,hr,MIN(sec) AS minsec FROM cte2
	GROUP BY dt,hr	
)
--两表连接得到你要的数据
SELECT a.id,a.时间,a.值 
FROM cte2 a
INNER JOIN cte3 b ON a.sec=b.minsec AND a.dt=b.dt AND a.hr=b.hr
大致是这样了[/quote] 以上是sql server的，没注意你发的是ACCESS版，ACCESS应该是用不了。但是逻辑大概就是上面那样
听雨停了 2018-01-26
  • 打赏
  • 举报
 回复
引用 2 楼 cqzjb 的回复:
[quote=引用 1 楼 qq_37170555 的回复:]
完全没看懂你要的效果怎么来的

不好意思，每表达清楚，意思是程序每个30秒采集了一条数据并记录到数据库，现在需要查询这个数据，但是不需要看每30秒的数据，而且需要看每1个小时的那一条数据即可。
不知是否表达清楚
望赐教。[/quote]


use Tempdb

go

--> --> 听雨停了-->生成测试数据

 

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

	drop table #tab

Go

Create table #tab([id] int,[时间] Datetime,[值] int)

 

Insert #tab

select 1,'2018-1-1 20:03:04',5 union all

select 2,'2018-1-1 20:03:34',7 union all

select 2,'2018-1-1 20:04:04',17 union all

select 2,'2018-1-1 20:04:34',6 union all

select 2,'2018-1-1 20:05:04',9 union all

select 2,'2018-1-1 20:06:34',1 UNION ALL

select 32,'2018-1-1 21:03:04',11 UNION ALL

select 33,'2018-1-1 21:03:34',12 UNION ALL

select 73,'2018-1-1 22:03:08',7 UNION ALL

select 74,'2018-1-1 22:03:38',8 

GO

--测试数据结束



;WITH cte AS (

	--找到整个表中最小的时间，从这个时间开始算起

	Select MIN(时间) as mintime from #tab	

),

cte2 AS (

	--每个时间减去最小时间得到的秒数

	SELECT *,CAST(时间 as date) as dt,datepart(hour,时间) as hr,datediff(second,mintime,时间) as sec

	FROM #tab,cte

),

cte3 AS (

	--按日期，小时分组得到每个日期，小时中最小的秒数

	SELECT dt,hr,MIN(sec) AS minsec FROM cte2

	GROUP BY dt,hr	

)

--两表连接得到你要的数据

SELECT a.id,a.时间,a.值 

FROM cte2 a

INNER JOIN cte3 b ON a.sec=b.minsec AND a.dt=b.dt AND a.hr=b.hr


大致是这样了
听雨停了 2018-01-25
  • 打赏
  • 举报
 回复
完全没看懂你要的效果怎么来的
cqzjb 2018-01-25
  • 打赏
  • 举报
 回复
引用 1 楼 qq_37170555 的回复:
完全没看懂你要的效果怎么来的
不好意思，每表达清楚，意思是程序每个30秒采集了一条数据并记录到数据库，现在需要查询这个数据，但是不需要看每30秒的数据，而且需要看每1个小时的那一条数据即可。 不知是否表达清楚 望赐教。
Mysql取每小时的一条记录
SELECT * FROM ( SELECT s.sensor_data,s.get_date,s.sensor_name, DATE(get_date) AS d, SUBSTRING(get_date,12,2) as h FROM t_env_sensor s where s.sensor_type=5 and s.mod_i
Mysql语句查询统计一天中每个小时数据的数量统计
floor函数是mysql的向下取整函数如floor(1.2)=1。Mysql查询一天中每半小时记录的数量。每个小时内卖出的订单总数。
es取每个小时的第一条数据
es在一段时间范围内获取每个小时的第一条数据 如下示例是查询一天中生产的huawei_phone，取出每个小时生产的第一部手机的信息 { "size":0, "_source":false, "query":{ "bool":{ "filter":[ { "mat...
mysql 分组按时间取最大_mysql 分组group by排序取每条记录中，时间最大的一条记录...
本文介绍了mysql 分组 group by, 排序 取每条记录中，时间最大的一条记录的实现方法，有需要的朋友可以参考一席。以下是 test 表，测试sqlCREATE TABLE IF NOT EXISTS `test` (CREATE TABLE IF NOT EXISTS `test` (`...
MongoTemplate分组排序取第一条记录
MongoTemplate分组排序取第一条记录 在开发过程中，竟然遇到这样一个需求，按照某一个字段或者某个查询条件进行分组。分组后取其最新的一条数据。 例如：在查询文件版本时，要求根据文件路径filePath分组，表示每一...
Access

7,733

社区成员

28,799

社区内容

发帖
与我相关
我的任务
社区描述
Microsoft Office Access是由微软发布的关系数据库管理系统。它结合了 MicrosoftJet Database Engine 和 图形用户界面两项特点。
社区管理员
  • Access
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

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

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

+ 用AI写文章