一个存储过程

USE [JZ_GPSDB]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[ups_GetDeviceStatisticsInfo] 
@StationID  varchar(10),
@ErrorValue  integer
AS
BEGIN
	-- 不返回受影响的行数
	SET NOCOUNT ON
	
	declare  @fault integer,@leave integer,@online integer--统计结果

	declare  @ConstructionID   integer
	declare  @ConstructionName varchar(20)
	declare  @count integer --工地数
	declare  @i     integer

	--创建工地临时表
	select ROW_NUMBER() over(order by ID) as RID,ConstructionID,ConstructionName into #Construction
    from  Construction  where StationID = @StationID
	--创建统计临时表
	Create table #tempStatistics(ConstructionID integer,ConstructionName varchar(20),fault integer,leave integer,isOnline integer)

	select @count = count(*) from #Construction

	--遍历临时工地表
	set @i = 1
	while @count >= @i
    BEGIN
       select @ConstructionID=ConstructionID,@ConstructionName=ConstructionName from #Construction where RID = @i; --获取当前行的博主账号
       
	   --故障个数
	   select @fault = count(*) from ##GpsRealtimeData,TankInfo,Construction where 
	      TankInfo.GPSID = ##GpsRealtimeData.GpsID and TankInfo.StationID =@StationID and CONVERT(int,Gweight) < @ErrorValue
		  and Construction.ConstructionID = tankInfo.ConstructionID and ConstructionName = @ConstructionName
       --离线个数
	   select @leave = count(*) from ##GpsRealtimeData,TankInfo,Construction where 
	      TankInfo.GPSID = ##GpsRealtimeData.GpsID and TankInfo.StationID =@StationID and DATEDIFF(n,DatapackTime,getdate()) > 3
		  and Construction.ConstructionID = tankInfo.ConstructionID and ConstructionName = @ConstructionName
	   --在线个数
	   select @online= count(*) from ##GpsRealtimeData,TankInfo,Construction where 
	      TankInfo.GPSID = ##GpsRealtimeData.GpsID and TankInfo.StationID =@StationID and DATEDIFF(n,DatapackTime,getdate()) < 3
		  and Construction.ConstructionID = tankInfo.ConstructionID and ConstructionName = @ConstructionName
       --添加到临时表中
	   insert into #tempStatistics(ConstructionID,ConstructionName,fault,leave,isOnline)values(@ConstructionID,
	      @ConstructionName,@fault,@leave,@online)

       set @i = @i + 1;
    END;
	------------------------------------------
	----未分配工地统计
	--故障数量
	with
	temp0
	as
	(
		select  count(*)as nCount from TankInfo,##GpsRealtimeData
		where
			(TankInfo.ConstructionID not in (select Construction.ConstructionID from Construction )
		and 
			##GpsRealtimeData.GpsID = TankInfo.GPSID)
		and CONVERT(int,Gweight) < @ErrorValue--在线且无故障
		and StationID = @StationID
	)
	select @fault = sum(nCount)  from Temp0;
	--离线数量
	with
	temp1
	as
	(
		select  count(*)as nCount from TankInfo,##GpsRealtimeData
		where
			(TankInfo.ConstructionID not in (select Construction.ConstructionID from Construction )
		and 
			##GpsRealtimeData.GpsID = TankInfo.GPSID)
		and DATEDIFF(n,DatapackTime,getdate()) > 3 and CONVERT(int,Gweight) > @ErrorValue--离线且无故障
		and StationID = @StationID
	)
	select @leave = sum(nCount)  from temp1;
	--在线数量
	with
	temp2
	as
	(
		select  count(*)as nCount from TankInfo,##GpsRealtimeData
		where
			(TankInfo.ConstructionID not in (select Construction.ConstructionID from Construction )
		and 
			##GpsRealtimeData.GpsID = TankInfo.GPSID)
		and DATEDIFF(n,DatapackTime,getdate()) < 3 and CONVERT(int,Gweight) > @ErrorValue--在线且无故障
		and StationID = @StationID
	)
	select @online=sum(nCount)  from Temp2;

	------------------------------------------
	insert into #tempStatistics(ConstructionID,ConstructionName,fault,leave,isOnline)values(@ConstructionID+1,
	      '未分配工地',@fault,@leave,@online)
	------------------------------------------
	select * from #tempStatistics

	drop table #Construction
	drop table #tempStatistics
END
go


----测试
--select * from Construction
--select * from TankInfo
--exec ups_GetDeviceStatisticsInfo '10001',-80000