关于表中前一天数据减去后一天数据

先根据需求 将需要的数据筛选出来建成一张临时表 ,这里把非整数型的数据取整了一下

 SELECT   CONVERT(char(12),CDATETIME_CREATED,111)AS 日期 ,CDEVICE_NO, convert(int,convert(numeric(18,9),CENERGY_VALUE)) AS 用水量
	INTO #table1
	FROM TBL_EAP_ENERGY_LOG	
	WHERE CDEVICE_NO LIKE '501%'and DATEDIFF(day,CDATETIME_CREATED,GETDATE())!=0 
	GROUP BY CDATETIME_CREATED,CDEVICE_NO,CENERGY_VALUE
	ORDER BY CDATETIME_CREATED

将得到的散乱数据 根据日期排序创建出两张相差一天的临时表

 SELECT ROW_NUMBER() OVER(ORDER BY 日期 asc) AS 序号, 日期, 
MAX(CASE CDEVICE_NO WHEN '50100015069' THEN 用水量 ELSE 0 END) AS '生活区',
MAX(CASE CDEVICE_NO WHEN '50100015044' THEN 用水量 ELSE 0 END) AS '一区',
MAX(CASE CDEVICE_NO WHEN '50100015051' THEN 用水量 ELSE 0 END) AS '二区'
INTO #table2
FROM #table1
WHERE DATEDIFF(day,日期,GETDATE())<6
GROUP BY 日期

SELECT ROW_NUMBER() OVER(ORDER BY 日期 asc) AS 序号,日期, 
MAX(CASE CDEVICE_NO WHEN '50100015069' THEN 用水量 ELSE 0 END) AS '生活区',
MAX(CASE CDEVICE_NO WHEN '50100015044' THEN 用水量 ELSE 0 END) AS '一区',
MAX(CASE CDEVICE_NO WHEN '50100015051' THEN 用水量 ELSE 0 END) AS '二区'
INTO #table3
FROM #table1
WHERE DATEDIFF(day,日期,GETDATE())<7 AND DATEDIFF(day,日期,GETDATE())>1
GROUP BY 日期

SELECT SUBSTRING(b.日期,6,8) 日期, CAST(b.生活区 AS INT) -CAST(a.生活区 AS INT) AS 生活区,CAST(b.一区 AS INT) -CAST(a.一区 AS INT) AS 一区,CAST(b.二区 AS INT) -CAST(a.二区 AS INT) AS 二区
 FROM #table3 a LEFT JOIN  #table2 b ON a.序号=b.序号

直接用CSAT函数将数据转为int型 根据序号关联两张表 然后直接相减就得到两天数据的差值