sql语句优化：
1。表加索引
2。少用like,直接用=所有值
3.where语句把能大量筛查的条件写在前面
4.数据量大时，参与计算的值相同时只取一条
后一条减前一条，，
select houec,[houdate],houtimes
,tw=isnull(convert(decimal(18,2),houtw-(select top 1 qiantw from #qian where houec=qianec and houcid>qiancid order by qiancid desc)),0)
,mins=isnull(datediff (minute ,(select top 1 qiandatetimes from #qian where houec=qianec and houcid>qiancid order by qiancid desc),houdatetimes),0)
from #hou
其中：
1 临时表#hou与#qian为同一个表中的数据，为方便计算写入两个临时表；
2 ec为设备编号，id为表的id号，相同的设备中id号唯一，houec=qianec and houcid>qiancid 限定了相同设备中相临id号的记录相减。
select top 1 qiantw from #qian where houec=qianec and houcid>qiancid order by qiancid desc
为查询houec=qianec 而且houcid>qiancid相同设备中相临id号的记录相减
日报
drop table #qian
create table #qian(qiancid nvarchar(20),qianec nvarchar(20) null, qiandatetimes smalldatetime null, qiandate datetime null,
qiantimes varchar(20) null,qiantw float null,
constraint [pk_electricqian] primary key clustered
(
[qiancid] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]
) on [primary]
insert into #qian
select min(cid) as qiancid,cequipmentcode as qianec,
min(dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)))as qiandatetimes ,
convert(varchar(10),dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)),120) as qiandate,
str(datepart(hh,dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)))) + ':00' as qiantimes,
cast(ctotalyield as float) as qiantw
from inverterdata_201310
where convert(varchar(10),dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)),120) = '2013-11-12' and ctotalyield'na' and ctotalyield'' and ctotalyield is not null and ctotalyield'0' and ctotalyield'#inv#' and ctotalyield'nan'
group by cequipmentcode,convert(varchar(10),dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)),120),datepart(hh,dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120))),ctotalyield
union select min(cid) as qiancid,cequipmentcode as qianec,
min(dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)))as qiandatetimes ,
convert(varchar(10),dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)),120) as qiandate,
str(datepart(hh,dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)))) + ':00' as qiantimes,
cast(ctotalyield as float) as qiantw
from inverterdata_201311
where convert(varchar(10),dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)),120) = '2013-11-12' and ctotalyield'na' and ctotalyield'' and ctotalyield is not null and ctotalyield'0' and ctotalyield'#inv#' and ctotalyield'nan'
group by cequipmentcode,convert(varchar(10),dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)),120),datepart(hh,dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120))),ctotalyield
union select min(cid) as qiancid,cequipmentcode as qianec,
min(dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)))as qiandatetimes ,
convert(varchar(10),dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)),120) as qiandate,
str(datepart(hh,dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)))) + ':00' as qiantimes,
cast(ctotalyield as float) as qiantw
from inverterdata_201312
where convert(varchar(10),dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)),120) = '2013-11-12' and ctotalyield'na' and ctotalyield'' and ctotalyield is not null and ctotalyield'0' and ctotalyield'#inv#' and ctotalyield'nan'
group by cequipmentcode,convert(varchar(10),dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)),120),datepart(hh,dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120))),ctotalyield
drop table #hou
create table #hou(houcid nvarchar(20),houec nvarchar(20) null,houdatetimes smalldatetime null, houdate smalldatetime null,houtimes varchar(20) null,houtw float null,
constraint [pk_electrichou] primary key clustered
(
[houcid] asc
)with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on) on [primary]
) on [primary]
insert into #hou
select min(cid) as houcid,cequipmentcode as houec,min(dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)))as houdatetimes ,convert(varchar(10),dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)),120) as houdate,
str(datepart(hh,dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)))) + ':00' as houtimes,
cast(ctotalyield as float) as houtw
from inverterdata_201310
where convert(varchar(10),dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)),120)='2013-11-12' and ctotalyield'na' and ctotalyield'' and ctotalyield is not null and ctotalyield'0' and ctotalyield'#inv#' and ctotalyield'nan'
group by cequipmentcode,convert(varchar(10),dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)),120),datepart(hh,dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120))),ctotalyield
union
select min(cid) as houcid,cequipmentcode as houec,min(dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)))as houdatetimes ,convert(varchar(10),dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)),120) as houdate,
str(datepart(hh,dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)))) + ':00' as houtimes,
cast(ctotalyield as float) as houtw
from inverterdata_201311
where convert(varchar(10),dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)),120)='2013-11-12' and ctotalyield'na' and ctotalyield'' and ctotalyield is not null and ctotalyield'0' and ctotalyield'#inv#' and ctotalyield'nan'
group by cequipmentcode,convert(varchar(10),dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)),120),datepart(hh,dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120))),ctotalyield
union
select min(cid) as houcid,cequipmentcode as houec,min(dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)))as houdatetimes ,convert(varchar(10),dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)),120) as houdate,
str(datepart(hh,dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)))) + ':00' as houtimes,
cast(ctotalyield as float) as houtw
from inverterdata_201312
where convert(varchar(10),dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)),120)='2013-11-12' and ctotalyield'na' and ctotalyield'' and ctotalyield is not null and ctotalyield'0' and ctotalyield'#inv#' and ctotalyield'nan'
group by cequipmentcode,convert(varchar(10),dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120)),120),datepart(hh,dateadd(hh,0,convert(smalldatetime,(cdate+' '+ctime),120))),ctotalyield