34,590
社区成员
发帖
与我相关
我的任务
分享
use Tempdb
go
--> --> 中国风(Roy)生成測試數據
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([ID] int,[PID] int,[PData] int,[DTTime] Datetime)
Insert #T
select 1,1,50,'2016-11-21 16:01:42.000' union all
select 2,2,58,'2016-11-21 16:01:31.000' union all
select 3,1,27,'2016-11-21 16:02:23.000' union all
select 4,2,30,'2016-11-21 16:02:35.000' union all
select 5,1,22,'2016-11-21 16:03:03.000' union all
select 6,2,31,'2016-11-21 16:03:22.000' union all
select 7,3,28,'2016-11-21 16:03:13.000' union all
select 8,1,45,'2016-11-21 16:04:14.000' union all
select 9,2,67,'2016-11-21 16:04:26.000' union all
select 10,3,29,'2016-11-21 16:04:51.000' union all
select 11,1,66,'2016-11-21 16:05:13.000' union all
select 12,2,39,'2016-11-21 16:05:46.000' union all
select 13,3,18,'2016-11-21 16:05:14.000' union all
select 14,1,57,'2016-11-21 16:06:33.000' union all
select 15,2,42,'2016-11-21 16:06:02.000' union all
select 16,1,23,'2016-11-21 16:07:26.000' union all
select 17,2,53,'2016-11-21 16:07:25.000'
Go
Select * FROM #T WHERE DATEPART(n,[DTTime])%2=DATEPART(n,(SELECT MIN([DTTime]) FROM #T))%2
/*
ID PID PData DTTime
1 1 50 2016-11-21 16:01:42.000
2 2 58 2016-11-21 16:01:31.000
5 1 22 2016-11-21 16:03:03.000
6 2 31 2016-11-21 16:03:22.000
7 3 28 2016-11-21 16:03:13.000
11 1 66 2016-11-21 16:05:13.000
12 2 39 2016-11-21 16:05:46.000
13 3 18 2016-11-21 16:05:14.000
16 1 23 2016-11-21 16:07:26.000
17 2 53 2016-11-21 16:07:25.000
*/
-- =============================================
-- Author: JackChan
-- Create date: <Create Date, ,>
-- Description: <Description, ,>
-- =============================================
USE tempdb
GO
if object_id('[Tb_PInfo]') is not null drop table [Tb_PInfo]
go
create table Tb_PInfo([ID] int identity(1,1),[PARTNAME] varchar(20))
insert [Tb_PInfo]
select '设备1' union all
select '设备2' union all
select '设备3'
GO
if object_id('[Tb_PDataList]') is not null drop table [Tb_PDataList]
go
create table Tb_PDataList([ID] int identity(1,1),[PID] INT,[Pdata] INT ,DTTime DATETIME)
insert [Tb_PDataList]
SELECT 1,50,'2016-11-21 16:01:42.000' UNION all
SELECT 2,58,'2016-11-21 16:01:31.000' UNION all
SELECT 1,27,'2016-11-21 16:02:23.000' UNION all
SELECT 2,30,'2016-11-21 16:02:35.000' UNION all
SELECT 1,22,'2016-11-21 16:03:03.000' UNION all
SELECT 2,31,'2016-11-21 16:03:22.000' UNION all
SELECT 3,28,'2016-11-21 16:03:13.000' UNION all
SELECT 1,45,'2016-11-21 16:04:14.000' UNION all
SELECT 2,67,'2016-11-21 16:04:26.000' UNION all
SELECT 3,29,'2016-11-21 16:04:51.000' UNION all
SELECT 1,66,'2016-11-21 16:05:13.000' UNION all
SELECT 2,39,'2016-11-21 16:05:46.000' UNION all
SELECT 3,18,'2016-11-21 16:05:14.000' UNION all
SELECT 1,57,'2016-11-21 16:06:33.000' UNION all
SELECT 2,42,'2016-11-21 16:06:02.000' UNION all
SELECT 1,23,'2016-11-21 16:07:26.000' UNION all
SELECT 2,53,'2016-11-21 16:07:25.000'
GO
SELECT * FROM Tb_PInfo AS tp
SELECT * FROM Tb_PDataList AS tpl
--从首条数据计算2分钟时间差,时间向前2分钟;当期时间分钟的基偶影响结果集做下处理
Select ID,PID,Pdata,DTTime FROM
(SELECT ID,PID,Pdata,DTTime,
datediff(mi,DTTime,DATEADD(mi,-2,CASE WHEN DATEPART(mi,GETDATE())%2=0 THEN DATEADD(mi,1,GETDATE()) ELSE GETDATE()END)) AS interval
FROM Tb_PDataList ) AS t WHERE t.interval%2 = 0