当然~~~ 只要效率高就可以!! 我后来改成这样,但是速度还是很慢 ALTER proc [dbo].[Depth] (@CustomerID bigint ) as declare @i bigint,@mid bigint,@max bigint declare @Date datetime,@CountIp varchar(200),@Depth int Update Count_ReturnAndDepth set StatisticsDepthOrFalse=2 where StatisticsDepthOrFalse=0 and CustomerID=@CustomerIDselect @mid=min(ID),@max=max(ID) from Count_ReturnAndDepth where CustomerID=@CustomerID and StatisticsDepthOrFalse=2 set @i=@mid while @i<@max begin select HostName,CONVERT(CHAR(13), CountTime, 120 )+':00:00', CountIp,count(CountUrl) from Count_ReturnAndDepth where CustomerID=1 and StatisticsDepthOrFalse=1 group by CountIp, CONVERT(CHAR(13), CountTime, 120 )+':00:00',HostName if exists(select 1 from Count_DepthInformation where CustomerID=@CustomerID and CountIP=@CountIp and PostTime=@Date) update Count_DepthInformation set DepthCount=DepthCount+(select DepthCount from Count_DepthInformation where CustomerID=@CustomerID and CountIP=@CountIp and PostTime=@Date) where CustomerID=@CustomerID and CountIP=@CountIp and PostTime=@Date else insert into Count_DepthInformation (CustomerID,CountIp,DepthCount,PostTime) values (@CustomerID,@CountIp,'1',@Date)set @i=@i+1 end Update Count_ReturnAndDepth set StatisticsDepthOrFalse=1 where StatisticsDepthOrFalse=2 and CustomerID=@CustomerID
ALTER proc [dbo].[Depth] as begin Update Count_ReturnAndDepth set StatisticsDepthOrFalse=2 where StatisticsDepthOrFalse=0 update a set DepthCount =DepthCount + b.sl --更新已存在的数量 from Count_DepthInformation a join ( select CustomerID,CountIp,CONVERT(CHAR(13), CountTime, 120 )+':00:00' as dt ,count(CountUrl) as sl from Count_ReturnAndDepth where StatisticsDepthOrFalse = 2 group by CustomerID,CountIp,CONVERT(CHAR(13), CountTime, 120 )+':00:00' ) b on a.CountIp =b.CountIp and a.CustomerID =b.CustomerID and a. =b.dt
insert into Count_DepthInformation (CustomerID,CountIp,DepthCount,PostTime) --插入新的统计 select CustomerID,CountIp,dt ,sl from ( select CustomerID,CountIp,CONVERT(CHAR(13), CountTime, 120 )+':00:00' as dt ,count(CountUrl) as sl from Count_ReturnAndDepth where StatisticsDepthOrFalse = 2 group by CustomerID,CountIp,CONVERT(CHAR(13), CountTime, 120 )+':00:00' ) b where not exists (select 1 from Count_DepthInformation where b.CustomerID =CustomerID and b.CountIp = CountIp and PostTime =dt ) Update Count_ReturnAndDepth set StatisticsDepthOrFalse=1 where StatisticsDepthOrFalse=2end ----- 改这个试试
只要效率高就可以!!
我后来改成这样,但是速度还是很慢
ALTER proc [dbo].[Depth]
(@CustomerID bigint )
as
declare @i bigint,@mid bigint,@max bigint
declare @Date datetime,@CountIp varchar(200),@Depth int
Update Count_ReturnAndDepth set StatisticsDepthOrFalse=2 where StatisticsDepthOrFalse=0 and CustomerID=@CustomerIDselect @mid=min(ID),@max=max(ID) from Count_ReturnAndDepth where CustomerID=@CustomerID and StatisticsDepthOrFalse=2
set @i=@mid
while @i<@max
begin
select HostName,CONVERT(CHAR(13), CountTime, 120 )+':00:00',
CountIp,count(CountUrl)
from Count_ReturnAndDepth where CustomerID=1 and StatisticsDepthOrFalse=1 group by CountIp,
CONVERT(CHAR(13), CountTime, 120 )+':00:00',HostName if exists(select 1 from Count_DepthInformation where CustomerID=@CustomerID and CountIP=@CountIp and
PostTime=@Date)
update Count_DepthInformation set DepthCount=DepthCount+(select DepthCount from Count_DepthInformation where CustomerID=@CustomerID and CountIP=@CountIp and PostTime=@Date) where CustomerID=@CustomerID
and CountIP=@CountIp and PostTime=@Date
else
insert into Count_DepthInformation (CustomerID,CountIp,DepthCount,PostTime)
values (@CustomerID,@CountIp,'1',@Date)set @i=@i+1
end
Update Count_ReturnAndDepth set StatisticsDepthOrFalse=1 where StatisticsDepthOrFalse=2 and CustomerID=@CustomerID
然后看这个ip这个时段是否存在于这个表中Count_ReturnAndDepth ,存在则加上这个深度,没有则插入一条新的数据!
Count_ReturnAndDepth
ID,CustomerID,CountIp,CountTime,HostName,StatisticsDepthOrFalse(是否统计过)
Count_DepthInformation
ID,CustomerID,CountIP,DepthCount(深度),PostTime(时段)
as
begin Update Count_ReturnAndDepth set StatisticsDepthOrFalse=2
where StatisticsDepthOrFalse=0 update a set DepthCount =DepthCount + b.sl --更新已存在的数量
from Count_DepthInformation a
join
(
select CustomerID,CountIp,CONVERT(CHAR(13), CountTime, 120 )+':00:00' as dt ,count(CountUrl) as sl
from Count_ReturnAndDepth
where StatisticsDepthOrFalse = 2
group by CustomerID,CountIp,CONVERT(CHAR(13), CountTime, 120 )+':00:00'
) b on a.CountIp =b.CountIp and a.CustomerID =b.CustomerID and a. =b.dt
insert into Count_DepthInformation (CustomerID,CountIp,DepthCount,PostTime) --插入新的统计
select CustomerID,CountIp,dt ,sl
from
(
select CustomerID,CountIp,CONVERT(CHAR(13), CountTime, 120 )+':00:00' as dt ,count(CountUrl) as sl
from Count_ReturnAndDepth
where StatisticsDepthOrFalse = 2
group by CustomerID,CountIp,CONVERT(CHAR(13), CountTime, 120 )+':00:00'
) b
where not exists (select 1 from Count_DepthInformation where b.CustomerID =CustomerID and b.CountIp = CountIp and PostTime =dt ) Update Count_ReturnAndDepth set StatisticsDepthOrFalse=1
where StatisticsDepthOrFalse=2end
-----
改这个试试