22,209
社区成员
发帖
与我相关
我的任务
分享
这个是动态语句,里的,我打印出来好看一些。也就是下面一句是一起执行。
以前 是 insert into tb_tmp select.... 但是一百多次这样的操作会出错几次,后来我改成了先create tb_tmp 再 insert into 后,正常了,正常运行了大概3个月左右。服务器上就装了一个sql server与一个调用到这个存储过程的应用程序,现在每天又会出现一几次错误了。
重点是,数据库的现在统计的数据量还不有以前大,反而还出现了错误。 下面的tb_tmp表是用了以后就删除的,这里没有用临时表,因为要多次连接处理,不是一次处理完的。
if object_id('tb_tmp') is not null
drop table tb_tmp;
create table tb_tmp([rn] int identity(1,1) primary key not null,siteId int,adId int,showIp int,clickIp int)
insert into tb_tmp select t.siteId,t.adId,count(1) as ShowIp,0 as ClickIp
from(
select distinct siteid,adid,ip from tb a where time>='xxx' and time<='xxx'
except
select siteid,adid,ip from tb_abs
) t group by t.siteid,t.adid
--不清楚,就是觉得你这样写开销太大,不要用execpt
insert into tb_tmp
select siteid,adid,count(distinct ip) as ShowIp,0 as ClickIp
from tb a where time>='xxx' and time<='xxx'
and not exists(select 1 from tb_abs where siteid=a.siteid
and adid=a.adid and ip=a.ip)
group by siteid,adid
这样写不行吗:
select * from(
select distinct siteid,adid,ip from tb a where time>='xxx' and time<='xxx'
except
select siteid,adid,ip from tb_abs
) t group by t.siteid,t.adid
不要那个临时表
select t.siteId,t.adId,count(1) as ShowIp,0 as ClickIp
from(
select distinct siteid,adid,ip from tb a
where time>='xxx' and time<='xxx'
and not exists (select 1 from tb_abs b where a.siteid=b.siteid and a.adid=b.adid and a.ip=b.ip)
) t group by t.siteid,t.adid
INSERT INTO tb_tmp
(
siteid,
adid,
ShowIp,
ClickIp
)
SELECT
a.siteid,
a.adid,
ShowIp = COUNT(DISTINCT a.ip),
ClickIp = 0
FROM tb a
WHERE a.[time] >= 'xxx'
AND a.[time] <= 'xxx'
AND NOT EXISTS(SELECT TOP(1) 1 FROM tb_abs WHERE siteid = a.siteid AND adid = a.adid AND ip = a.ip)
GROUP BY a.siteid, a.adid