sql语句优化及后一条减前一条

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

select CONVERT(varchar(10), dateadd(hh,1, convert(SMALLDATETIME,([houdate]+' '+houtimes),120)), 120 ) as hdate,
CONVERT(varchar(5), dateadd(hh,1, convert(SMALLDATETIME,([houdate]+' '+houtimes),120)), 108 ) as htime,
sum(tw) as yield
from
(
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
) b left join EquipmentInTime on cequipmentname=houec
where tw>=0 and tw<=caccuratecapacity*2*mins/60
group by [houdate],houtimes
order by [houdate],houtimes

月报

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(7),dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)),120) = '2013-11' 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(7),dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)),120) = '2013-11' 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(7),dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)),120) = '2013-11' 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

select convert(varchar(10),[houdate],120) as houdate,sum(tw) as yield
from
(
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 (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(7),dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)),120)='2013-11' 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(7),dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)),120)='2013-11' 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(7),dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)),120)='2013-11' 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
)d
) b,EquipmentInTime
where tw>=0 and tw<=caccuratecapacity*2*mins/60 and EquipmentInTime.cequipmentname=houec
group by [houdate]
order by [houdate]

drop table #qian

年报

drop table #qianhou

create table #qianhou(houMonth varchar(7),tw decimal(18,2),
CONSTRAINT [PK_Electricqianhou] PRIMARY KEY CLUSTERED
(
[houMonth] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

drop table #qian

CREATE TABLE #qian(qiancid nvarchar(20),qianec nvarchar(20) null,qiantw float null,
qiandatetimes smalldatetime 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]

--------------------------循环12个月份表-------------------------------------------

INSERT INTO #qian
select max(cid) as qiancid , cequipmentcode as qianec,cast(ctotalyield as float) as qiantw,min(dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)))as qiandatetimes
from InverterData_201309 where convert(varchar(7),dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)),120) = '2013-09' and ctotalyield<>'na' and ctotalyield<>'' and ctotalyield is not null and ctotalyield<>'0' and ctotalyield<>'#INV#' and ctotalyield<>'nan'
group by cequipmentcode,ctotalyield
UNION
select max(cid) as qiancid , cequipmentcode as qianec,cast(ctotalyield as float) as qiantw,min(dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)))as qiandatetimes
from InverterData_201310 where convert(varchar(7),dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)),120) = '2013-09' and ctotalyield<>'na' and ctotalyield<>'' and ctotalyield is not null and ctotalyield<>'0' and ctotalyield<>'#INV#' and ctotalyield<>'nan'
group by cequipmentcode,ctotalyield
UNION
select max(cid) as qiancid , cequipmentcode as qianec,cast(ctotalyield as float) as qiantw,min(dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)))as qiandatetimes
from InverterData_201308
where convert(varchar(7),dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)),120) = '2013-09' and ctotalyield<>'na' and ctotalyield<>'' and ctotalyield is not null and ctotalyield<>'0' and ctotalyield<>'#INV#' and ctotalyield<>'nan'
group by cequipmentcode,ctotalyield

drop table #hou

CREATE TABLE #hou(houcid nvarchar(20),houec nvarchar(20) null,houdatetimes smalldatetime null, houdate smalldatetime 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 max(cid) as houcid,cequipmentcode as houec,
min(dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)))as houdatetimes ,
min(convert(varchar(10),dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)),120)) as houdate,
cast(ctotalyield as float) as houtw
from InverterData_201309
where convert(varchar(7),dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)),120) = '2013-09' and ctotalyield<>'na' and ctotalyield<>'' and ctotalyield is not null and ctotalyield<>'0' and ctotalyield<>'#INV#' and ctotalyield<>'nan'
group by cequipmentcode,ctotalyield
UNION
select max(cid) as houcid,cequipmentcode as houec,
min(dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)))as houdatetimes ,
min(convert(varchar(10),dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)),120)) as houdate,
cast(ctotalyield as float) as houtw
from InverterData_201310
where convert(varchar(7),dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)),120) = '2013-09' and ctotalyield<>'na' and ctotalyield<>'' and ctotalyield is not null and ctotalyield<>'0' and ctotalyield<>'#INV#' and ctotalyield<>'nan'
group by cequipmentcode,ctotalyield
UNION
select max(cid) as houcid,cequipmentcode as houec,
min(dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)))as houdatetimes ,
min(convert(varchar(10),dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)),120)) as houdate,
cast(ctotalyield as float) as houtw
from InverterData_201308
where convert(varchar(7),dateadd(hh,0,convert(SMALLDATETIME,(cdate+' '+ctime),120)),120) = '2013-09' and ctotalyield<>'na' and ctotalyield<>'' and ctotalyield is not null and ctotalyield<>'0' and ctotalyield<>'#INV#' and ctotalyield<>'nan'
group by cequipmentcode,ctotalyield

INSERT INTO #qianhou
select convert(varchar(7),[houdate],120) as houMonth,sum(tw) as yield
from
(
select tw=convert(decimal(18,2),houtw-(select top 1 qiantw from #qian where houcid>#qian.qiancid and houec=qianec order by #qian.qiancid desc))
,mins=DATEDIFF (minute ,(select top 1 qiandatetimes from #qian where houcid>#qian.qiancid and houec=qianec order by #qian.qiancid desc),houdatetimes)
,[houdate],houec
from #hou
) b left join EquipmentInTime
on EquipmentInTime.cequipmentname=houec
where tw>=0 and tw<=caccuratecapacity*2*mins/60
group by convert(varchar(7),[houdate],120)
order by houMonth

---------------------------------------------------
truncate table #qian
truncate table #hou