27,582
社区成员




if OBJECT_ID ('t1') is not null
drop table t1
go
create table t1 ( datevalue datetime,
plc1 decimal(19,4),
plc2 decimal(19,4)
)
go
insert into t1
select '2017-07-01 17:03:29.000', 1.120, 7.715 union all
select '2017-07-01 17:04:29.000', 1.5, 7.89 union all
select '2017-07-01 17:05:29.000', 1.7, 8.5 union all
select '2017-07-02 18:03:29.000', 2.3, 9.5 union all
select '2017-07-02 18:04:29.000', 2.5, 9.6 union all
select '2017-07-02 18:05:29.000', 2.7, 9.8 union all
select '2017-07-07 19:03:29.000', 3.5, 15.3 union all
select '2017-07-07 19:04:29.000', 3.6, 15.4 union all
select '2017-07-07 19:05:29.000', 3.7, 15.5 union all
select '2017-07-29 17:03:29.000', 4.1, 17.5 union all
select '2017-07-29 17:04:29.000', 4.2, 17.6 union all
select '2017-07-29 17:05:29.000', 4.3, 17.7
go
if OBJECT_ID ('plc_diff') is not null
drop function plc_diff
go
create function plc_diff (@date_name varchar(10), @plc varchar(10))
returns decimal(19,4)
as
begin
declare @plc_max_last decimal(19,4), @plc_max decimal(19,4), @plc_diff decimal(19,4)
if @plc = 'plc1'
begin
select @plc_max_last = max(plc1) from t1 where CONVERT(varchar(10),datevalue + 1,121) = @date_name
select @plc_max = max(plc1) from t1 where CONVERT(varchar(10),datevalue,121) = @date_name
end
if @plc = 'plc2'
begin
select @plc_max_last = max(plc2) from t1 where CONVERT(varchar(10),datevalue + 1,121) = @date_name
select @plc_max = max(plc2) from t1 where CONVERT(varchar(10),datevalue,121) = @date_name
end
set @plc_diff = @plc_max - @plc_max_last
return @plc_diff
end
go
select distinct CONVERT(varchar(10),datevalue,121) as 日期,
isnull(convert(varchar(30),dbo.plc_diff(CONVERT(varchar(10),datevalue,121), 'plc1')),'缺上一天数据') as plc1增量,
isnull(convert(varchar(30),dbo.plc_diff(CONVERT(varchar(10),datevalue,121), 'plc2')),'缺上一天数据') as plc2增量
from t1
go
drop function plc_diff
drop table t1
go
日期 plc1增量 plc2增量
-------------------------------------------
2017-07-01 缺上一天数据 缺上一天数据
2017-07-02 1.0000 1.3000
2017-07-07 缺上一天数据 缺上一天数据
2017-07-29 缺上一天数据 缺上一天数据
SELECT dTime1=CONVERT(date,dtime,120),plc11=SUM(plc1),plc12=SUM(plc2)
FROM t
GROUP BY CONVERT(date,dtime,120)
--SELECT DATEDIFF(d,'2019-01-25',GETDATE())
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[dtime] DATETIME
,[PLC1] DECIMAL(10,3)
,[PLC2] DECIMAL(10,3)
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'2017-07-01 17:03:29.000',N'1.120',N'7.715')
INSERT INTO dbo.[t] VALUES(N'2017-07-01 17:04:29.000',N'1.5',N'7.89')
INSERT INTO dbo.[t] VALUES(N'2017-07-01 17:05:29.000',N'1.7',N'8.5')
INSERT INTO dbo.[t] VALUES(N'2017-07-02 18:03:29.000',N'2.3',N'9.5')
INSERT INTO dbo.[t] VALUES(N'2017-07-02 18:04:29.000',N'2.5',N'9.6')
INSERT INTO dbo.[t] VALUES(N'2017-07-02 18:05:29.000',N'2.7',N'9.8')
----- 以上为测试表及测试数据
SELECT dTime1=CONVERT(date,dtime,120),plc11=SUM(plc1),plc12=SUM(plc2)
FROM t
GROUP BY CONVERT(date,dtime,120)
--SELECT DATEDIFF(d,'2019-01-25',GETDATE())
SELECT b.dTime2,b.plc21-a.plc11,b.plc22-a.plc12 FROM (
SELECT dTime1=CONVERT(date,dtime,120),plc11=SUM(plc1),plc12=SUM(plc2) --每天的统计表
FROM t
GROUP BY CONVERT(date,dtime,120)) a
LEFT JOIN (
SELECT dTime2=CONVERT(date,dtime,120),plc21=SUM(plc1),plc22=SUM(plc2) --每天的统计表
FROM t
GROUP BY CONVERT(date,dtime,120)) b ON DATEDIFF(d,a.dTime1,b.dTime2)=1 --前表和后表相差为1天的就是
dTime2 Plc1Diff Plc2Diff
2017-07-02 3.180 4.795
NULL NULL NULL
USE tempdb
GO
IF OBJECT_ID('dbo.[t]') IS NOT NULL
DROP TABLE dbo.[t]
GO
CREATE TABLE dbo.[t](
[dtime] DATETIME
,[PLC1] DECIMAL(10,3)
,[PLC2] DECIMAL(10,3)
)
GO
SET NOCOUNT ON
INSERT INTO dbo.[t] VALUES(N'2017-07-01 17:03:29.000',N'1.120',N'7.715')
INSERT INTO dbo.[t] VALUES(N'2017-07-01 17:04:29.000',N'1.5',N'7.89')
INSERT INTO dbo.[t] VALUES(N'2017-07-01 17:05:29.000',N'1.7',N'8.5')
INSERT INTO dbo.[t] VALUES(N'2017-07-02 18:03:29.000',N'2.3',N'9.5')
INSERT INTO dbo.[t] VALUES(N'2017-07-02 18:04:29.000',N'2.5',N'9.6')
INSERT INTO dbo.[t] VALUES(N'2017-07-02 18:05:29.000',N'2.7',N'9.8')
----- 以上为测试表及测试数据
---查询
;WITH cte AS (
SELECT
ROW_NUMBER() OVER (PARTITION BY CONVERT(CHAR(10),dtime,120) ORDER BY dtime) AS rid
,ROW_NUMBER() OVER (PARTITION BY CONVERT(CHAR(10),dtime,120) ORDER BY dtime DESC) AS rid2
,CONVERT(CHAR(10),dtime,120) AS d
,*
FROM t
)
SELECT
a.d
,(b.PLC1-a.PLC1) AS PLC1_DIFF
,(b.PLC2-a.PLC2) AS PLC2_DIFF
FROM cte AS a
INNER JOIN cte AS b ON a.d=b.d AND a.rid=1 AND b.rid2=1
/*
d PLC1_DIFF PLC2_DIFF
---------- --------------------------------------- ---------------------------------------
2017-07-01 0.580 0.785
2017-07-02 0.400 0.300
*/
SELECT *,B.PLC1-A.PLC1 AS PLC1_DIFF,B.PLC2-A.PLC2 AS PLC2_DIFF
FROM TABLE A
JOIN TABLE B ON DATEDIFF(DAY,A,DTIME,B.DTIME)=1 AND CONVERT(VARCHAR(5),A.DTIME,114)=CONVERT(VARCHAR(5),B.DTIME,114)