请教下sql语句的问题,SQLServer2008

细露仔 2019-01-24 04:24:46
有一张表,大概形式是
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的值)。请问这要怎么写?
...全文
244 8 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
8 条回复
切换为时间正序
请发表友善的回复…
发表回复
阿·修 2019-01-31
  • 打赏
  • 举报
回复

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	缺上一天数据	缺上一天数据
victor_yang 2019-01-26
  • 打赏
  • 举报
回复

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


这段是我刚才测试用的语句,不是答案
victor_yang 2019-01-26
  • 打赏
  • 举报
回复

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天总量的差,就是每天生产的总量
吉普赛的歌 2019-01-24
  • 打赏
  • 举报
回复
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
*/

极客诗人 2019-01-24
  • 打赏
  • 举报
回复
写的有点乱 大致意思就是分组到天 然后最大值-最小值
极客诗人 2019-01-24
  • 打赏
  • 举报
回复
select year(CreateTime),month(CreateTime),DAY(CreateTime),COUNT(*),max(a)-Min(a) from test_yymm
group by year(CreateTime),month(CreateTime),DAY(CreateTime)
RINK_1 2019-01-24
  • 打赏
  • 举报
回复

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)
shoppo0505 2019-01-24
  • 打赏
  • 举报
回复
先把每天的第一条取出来,再把最后一条取出来,然后取差。

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

27,582

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

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