7,732
社区成员




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
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
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
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
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
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