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

最新推荐文章于 2020-09-09 08:48:50 发布
最新推荐文章于 2020-09-09 08:48:50 发布
阅读量530 收藏 1
点赞数
文章标签： 数据库
原文链接：http://www.cnblogs.com/qiulang/p/3423294.html
版权

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

 

转载于:https://www.cnblogs.com/qiulang/p/3423294.html

18790970257
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
postgresql中的ltree类型使用方法
12-16
postgresql有很多比较妖的数据类型，ltree算一个。 简介 ltree是Postgresql的一个扩展类型 http://www.sai.msu.su/~megera…，在解决树形结构的数据存储上使用。 查看是否安装了插件 select * from pg_extension where extname = 'ltree'; 定义 字段的格式为：L1.L2.L3.L4.L5.L6….. 标签是一系列字母数字字符和下划线A-Za-z0-9_, 标签长度必须小于256个字节。标签路径是由点分隔的零个或多个标签的序列，例如L1.L2.L3，表示从分层树的根到特定节点的路径。
sql查询：将下一行减去上一行，并做相应的计算
keyboard专栏
07-06 5675
项目中，有一个小时获取一个数据的任务，获取具体的值后，需要存在数据库中。但是长期累计后，需要检查有没有做到一个小时记录一次。比如这样的数据： 我们需要统计，有没有遗漏的遗留，是不是有遗漏了一个小时的， 查询结果：这样就和容易查询出来，那些地方不是一个小时一次了。知识点： 使用sqlite的窗口函数...
sql server 分组后,取每组前1条数据
路虽远行则将至，事虽难做则必成
06-01 456
sql 排名开窗函数 ROW_NUMBER、DENSE_RANK、RANK、NTILE属于排名函数。 排名开窗函数可以单独使用ORDER BY 语句，也可以和PARTITION BY同时使用。 PARTITION BY用于将结果集进行分组，开窗函数应用于每一组。 ODER BY 指定排名开窗函数的顺序。在排名开窗函数中必须使用ORDER BY语句 [url]http://w...
事务ACID特性；脏读、不可重复读、幻读；读未提交、读已提交、可重复读、串行化
haoranhaoshi的博客
09-09 965
事务具有4个特征，分别是原子性、一致性、隔离性和持久性，简称事务的ACID特性； 一、原子性（atomicity) 一个事务要么全部提交成功，要么全部失败回滚，不能只执行其中的一部分操作，这就是事务的原子性 二、一致性（consistency) 事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处于一致性状态。 如果数据库系统在运行过程中发生故障，有些事务尚未完成就被迫中断，这些未完成的事务对数据库所作的修改有一部分已写入物理数据库，这是数据库就处于一种不正确
实现前后行相减的SQL该如何写？有两种方法！
热门推荐
yinn
01-14 2万+
背景 有时会有这样一种查询场景，需要对查询结果前后行操作，比如后一行减前一行。 比如有一张用户登录表，有登入和登出两行，需要相减简单计算在线时长。或者一张订单表，需要计算后一行的订单id和前一行的订单id差多少（假设订单号是单调递增的）。 针对这类场景，SQL语句改如何写呢？ 表结构和数据构造 以统计前后行的订单号是否连续为例， 表结构： CREATE TABLE `t_test3` ( `i...
利用sql语句复制一条或多条记录
09-06
sql 复制记录
执行一条sql语句update多条记录实现思路
09-10
如果你想更新多行数据，并且每行记录的各字段值都是各不一样，你会怎么办呢？本文以一个示例向大家讲解下如何实现如标题所示的情况，有此需求的朋友可以了解下
Mysql查询最近一条记录的sql语句(优化篇)
09-09
主要介绍了Mysql查询最近一条记录的sql语句，非常不错，具有一定的参考借鉴价值，需要的朋友参考下吧
SQL语句实现删除重复记录并只保留一条
09-10
主要介绍了SQL语句实现删除重复记录并只保留一条,本文直接给出实现代码,并给出多种查询重复记录的方法,需要的朋友可以参考下
MySQL SQL语句优化的10条建议
09-10
主要介绍了MySQL中SQL语句优化需要注意的10点,,特别是大型高并发网站,需要的朋友可以参考下
pyspark之dataframe当前行与上一行值求差
AcceptedLin的博客
07-17 3985
pyspark之dataframe当前行与上一行值求差 from pyspark import SparkContext from pyspark.sql import SQLContext from pyspark.sql import functions as F from pyspark.sql.window import Window sc = SparkContext(appName="PrevRowDiffApp") sqlc = SQLContext...
sql server 查询当前记录的前一条和后一条记录
weixin_34205826的博客
02-04 1114
　　举例如下： 　　一张员工表tb_user，有字段id、name、num(员工工号)，则查询id=20的员工的前一个员工和后一个员工的sql文分别是： 　　前一个员工：select top 1 * from tb_user where id &lt; 20 order by id desc（加上desc，数据库将从表末端开始查询）； 　　后一个员工：select top 1 * from ...
数据库根据时间后一条数据减去前一条数据的一种简单处理方式
yzy85的专栏
05-03 1万+
因为数据计算错误，导致数据库中连续时间保存的数据没有正确的计算后一个时间的数据前去前一个时间的数据，因此需要重新处理，但是由于数据量比较大，因此直接使用sql语句处理速度慢且sql语句更麻烦，因此，采用一种先用简单sql语句计算后一时间减去前一时间并保存到中间表，然后利用存储过程和游标将中间表计算好的数据循环update到原来的表中。
mysql_存储过程_后一行减去前一行
u013165852的博客
04-19 1161
mysql_存储过程_后一行减去前一行 DELIMITER $$ /*统计单个用户登录次数的存过 @times_count int 返回值 @i 记录行号的变量 初始值为0 @temp 记录时间差的变量 @total 记录登录次数的变量 初始值为0 @nums 记录共有多少行（单个用户） @j 记录行号的变量 值为i+1 */ CRE...
sqlserver用当前行减去前一行的数据的简单处理方法 -- 不使用循环
linybo的专栏
07-28 7209
;with cet -- 1.递归取出需要的数据 as  ( -- 对于CET通用表达式增加自定义列的问题？-- 附带说明 -- 自定义列必须满足下面二者中的一种,否则报错:[在递归查询 "cet" 的列 "CommType" 中，定位点类型和递归部分的类型不匹配。] -- a.自定义列CommType显示转换 -- b.或者在使用自定义列时，前面列的长度必须比后面列的长度大 s
给你一条sql语句你会怎么优化
最新发布
06-08
优化 SQL 语句的方法有很多种，我会根据具体情况采取不同的优化策略。以下是一些常见的优化方法： 1. 索引优化：通过创建恰当的索引，可以加快查询速度。 2. 优化查询语句：避免使用 SELECT *，只返回需要的字段；...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值