SQL行与行之间相减（电费计算）或 起止码算法或电价、班组算法、局部虚拟表、全局虚拟表

1、存储过程中尽量不要用全局虚拟表##

注意：先建表结构，然后insert into #RecordDatas exec('select * from tab')

2、正确删除临时表的方法：

if OBJECT_ID('tempdb..#临时表') is not null
drop table #临时表

注意：不管全局临时表、还是临时表用完了一定要drop table 

         临时表存放在tempDb数据库的临时表文件夹

3、实例：反复创建临时表不会报错（Sql2005 以后数据库才有）

create procedure [dbo].[proc_testtemptable]
as 
begin
  
   select  'aa' a into #a
   select * from #a

end

注意：对于存储过程的创建的临时表，没必要删除，对于满足可缓存的临时表对象，想删也删不掉！

反复创建临时表，不会报错，这就是缓存临时表。（但##全局虚拟表除外）

4、实例：执行DDL操作，比如创建索引等。不会缓存临时表

alter procedure [dbo].[proc_testtemptable]

as 

begin

create table #a (col_1 varchar(100))

  

create index idx_col1 on #a(col_1)

insert into #a values('aaa')

select * from #a

end

--查询实例级临时表创建次数
select * from sys.dm_os_performance_counters
where counter_name like '%Temp Tables Creation Rate%'
go
--执行存储过程，存储过程中包含临时表的创建
exec dbo.proc_testtemptable
go

--查询实例级临时表创建次数
select * from sys.dm_os_performance_counters
where counter_name like '%Temp Tables Creation Rate%'
go

效果：

5、并发线程之间当然不会重用同一个临时表，如果不是这样的话，SQL Server也不用混江湖了，并发的每个线程会创建自己的临时表。

一、行相减

方法一：

select *,ROW_NUMBER() OVER(PARTITION BY equipmentid ORDER BY Time) rn into ##2ADDF2D467BB4FD9849F958C1F16B8A3 

from  (select * from RecordDatas201610 where time>='2016-10-09 08:00:00' and time<='2016-10-10 08:00:00' ) a where varname='epi' 

select a.equipmentid,a.time time,b.maxvalue-a.maxvaluevalue into ##C0E93A4B345D47B48D41DCEADC489DAA 

from ##2ADDF2D467BB4FD9849F958C1F16B8A3 a,##2ADDF2D467BB4FD9849F958C1F16B8A3 b 

where a.equipmentid=b.equipmentid AND a.rn+1=b.rn

ALTER PROCEDURE [dbo].[用气量日报]
   @dtime datetime= N'2016-06-21'
AS
BEGIN

--时间范围
declare @stime varchar(50),@etime varchar(50),@ctime varchar(50),@STableName varchar(500),@ETableName varchar(500),@Ssql varchar(8000)
set @stime=CONVERT(char(10),@dtime,120)+' 08:00:00'
set @ctime=CONVERT(char(10),@dtime,120)+' 20:00:00'
set @etime=CONVERT(char(10),dateadd(day,1,@dtime),120)+' 08:00:00'

--获得数据源
set @STableName='RecordDatas'+CONVERT(char(6),@dtime,112)
if datepart(day,cast(@etime as datetime))=1
begin
	set @ETableName='RecordDatas'+CONVERT(char(6),cast(@etime as datetime),112)
	set @Ssql='select * from '+@STableName+' where time>='''+@stime+''' and time<='''+@etime+''' union all select * from '+@ETableName+' where time>='''+@stime+''' and time<='''+@etime+''' '
end
else 
begin
	set @ETableName=''
	set @Ssql='select * from '+@STableName+' where time>='''+@stime+''' and time<='''+@etime+''' '
end 

--print @Ssql


CREATE TABLE #RecordDatas(
	[EquipmentID] [int] NULL,
	[VarID] [int] NULL,
	[VarName] [nvarchar](50) NULL,
	[MaxValue] [float] NULL,
	[MaxValueTime] [datetime] NULL,
	[MinValue] [float] NULL,
	[MinValueTime] [datetime] NULL,
	[AvgValue] [float] NULL,
	[Time] [datetime] NULL,
	rn int
) ON [PRIMARY]

----编号
insert into #RecordDatas exec('select *,ROW_NUMBER() OVER(PARTITION BY equipmentid ORDER BY Time) rn  from ('+@Ssql+') a where varname=''Lj'' ')
----行减
select a.equipmentid,a.time time,b.maxvalue-a.maxvalue value into #RecordDatas2 from #RecordDatas a,#RecordDatas b where a.equipmentid=b.equipmentid AND a.rn+1=b.rn


create table #气表
(
 name   varchar(50),   
 equipmentid int,
 Idorder int
)

insert into #气表 select '车间1',404,1
insert into #气表 select '车间2',405,2
insert into #气表 select '窑尾',406,3
insert into #气表 select '立磨',407,4
insert into #气表 select '旋二',408,5

	select a.name,a.equipmentid,isnull(b.白班气量,0) 白班气量,isnull(c.夜班气量,0) 夜班气量,isnull(b.白班气量,0)+isnull(c.夜班气量,0) 日气量  
	into #zj
	from #气表 a
	left join(
		select equipmentid,sum(value) 白班气量  from #RecordDatas2
		where time>=@stime and  time<@ctime and equipmentid in(select equipmentid from #气表)
		group by equipmentid
    )b  on a.equipmentid=b.equipmentid
    left join(
		select equipmentid,sum(value) 夜班气量  from #RecordDatas2
		where time>=@ctime and  time<@etime and equipmentid in(select equipmentid from #气表)
		group by equipmentid
    )c on a.equipmentid=c.equipmentid  
    order by a.Idorder

END

方法二：

select t1.EquipmentID , t1.time ,t1.maxvalue - (select top 1 t2.maxvalue from 数据集 t2 where t2.time < t1.time and t2.EquipmentID = t1.EquipmentID ) 电量from 数据集 t1 

方法三：

SELECT  t1.EquipmentID 设备Id, t1.maxValue 电量1,t1.time 时间1,max(t2.time)与时间1相邻 FROM ##datarecord t1               left join ##datarecord t2            on t1.varid=t2.varid                and t1.EquipmentID=t2.EquipmentID           and t1.time>t2.time        where t2.maxValue>0 and t1.maxValue>0group by  t1.EquipmentID,t1.maxValue,t1.time

效果：