请教下sql语句的问题，SQLServer2008

[细露仔]

有一张表，大概形式是
dtime（datetime类型） PLC1 PLC2

2017-07-01 17:03:29.000 1.120 7.715
2017-07-01 17:04:29.000 1.5 7.89
2017-07-01 17:05:29.000 1.7 8.5
。
。
。
2017-07-02 18:03:29.000 2.3 9.5
2017-07-02 18:04:29.000 2.5 9.6
2017-07-02 18:05:29.000 2.7 9.8
。
。
。
2017-07-07 19:03:29.000 3.5 15.3
2017-07-07 19:04:29.000 3.6 15.4
2017-07-07 19:05:29.000 3.7 15.5
。
。
。
2017-07-29 17:03:29.000 4.1 17.5
2017-07-29 17:04:29.000 4.2 17.6
2017-07-29 17:05:29.000 4.3 17.7
。
。
。
数据只是想表明这几个数据都是递增的。
现在是想用sql语句获取某月内每天(即当天0:00至第二天0:00)内的增长值(第二天0:00的值减第一天0:00的值)。请问这要怎么写？

[shoppo0505]

先把每天的第一条取出来，再把最后一条取出来，然后取差。

取得时候可以使用rownumber，partition by等算法

[RINK_1]

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)

[极客诗人]

select year(CreateTime),month(CreateTime),DAY(CreateTime),COUNT(*),max(a)-Min(a) from test_yymm
group by year(CreateTime),month(CreateTime),DAY(CreateTime)

[极客诗人]

写的有点乱 大致意思就是分组到天 然后最大值-最小值

[吉普赛的歌]

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

[victor_yang]

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

借用楼上兄弟的数据，做2张相同的统计表联查，日期相差为1天的就是你要的数据，计算前后2天总量的差，就是每天生产的总量

[victor_yang]

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 ('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	缺上一天数据	缺上一天数据