根据用户ID查询辖区场所统计-优快云博客

本文链接：https://blog.youkuaiyun.com/smahorse/article/details/8115968
USE 
GO
/****** Object:  StoredProcedure [dbo].[Web_UnitManage_NSMC_tb_Unit_StatByUserID]    Script Date: 2012/10/26 16:20:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/*------------------------------------
-- 用途：根据用户ID查询辖区场所统计
-- 项目名称：PSM5.7.3
-- 说明：
-- 时间：2012-09-24
-- 编写者: 
--------------------------------------
-- 修改记录:
-- 编号	修改时间		修改人		修改原因		修改标注
------------------------------------*/
/****************************************************************
测试语句
EXEC [Web_UnitManage_NSMC_tb_Unit_StatByUserID] @UserID=0
****************************************************************/
ALTER PROCEDURE [dbo].[Web_UnitManage_NSMC_tb_Unit_StatByUserID]
(
	@UserID INT
)
AS 
	BEGIN
		SET NOCOUNT ON  
		DECLARE @SQL NVARCHAR(MAX) 
		--根据用户ID，将需要处理的地市存入临时表
		SELECT A.AreaCode,
				A.AreaID,
				A.AreaName
				INTO #TempArea
				FROM dbo.NSMC_tb_Area A WITH(NOLOCK)
				WHERE (@UserID=0 OR EXISTS (SELECT 1 
													FROM dbo.NSMC_re_UserArea WITH(NOLOCK) 
													WHERE AreaID=A.AreaID 
													AND UserID=@UserID))
				AND A.ParentCode NOT IN (-1,-2)
		--如果不存在需要处理的记录，直接退出
		IF NOT EXISTS (SELECT 1 FROM #TempArea)
			RETURN
		--建立临时表记录结果集
		CREATE TABLE #TempResult
		(
			AreaID INT,
			AreaCode NVARCHAR(6),
			PoliceStationID INT,
			AreaPoliceName NVARCHAR(30),
			HotelCount INT,
			SchoolCount INT,
			EnterpriseCount INT,
			WifiUnitCount INT,
			AllUnitCount INT,
			OnlineUnit INT,
			OfflineUnit INT,
			UnitOnlineRate NUMERIC(6,2),
			OnlineCustomTotal INT,
			AlertCount INT
		)          
		--先统计地区统计值
		SELECT U.AreaCode,
				COUNT(1) AS OnlineCustomTotal
				INTO #TempAreaComp
				FROM dbo.NSMC_log_Customer C WITH(NOLOCK)
				INNER JOIN dbo.NSMC_tb_Unit U WITH(NOLOCK)
					ON C.UnitID = U.UnitID
				WHERE U.GuildID<>1
				AND C.StartTime>= CONVERT(nvarchar(10),getdate(),120) 
				AND C.StartTime <= CONVERT(nvarchar(10),DATEADD(DAY,1,getdate()),120)
				GROUP BY U.AreaCode

		SELECT U.AreaCode,
				COUNT(1) AS AlertCount
				INTO #TempAreaAlert
				FROM dbo.NSMC_tb_Alert A WITH(NOLOCK)
				INNER JOIN dbo.NSMC_tb_Unit U WITH(NOLOCK)
					ON A.GuildID = U.GuildID AND A.UnitCode = U.UnitCode
				WHERE A.GuildID<>1 
				AND A.AlertDate >= CONVERT(nvarchar(10),getdate(),120) 
				AND A.AlertDate <= CONVERT(nvarchar(10),DATEADD(DAY,1,getdate()),120)
				GROUP BY U.AreaCode

		INSERT INTO #TempResult
		SELECT  MAX(TA.AreaID) AS AreaID,
				TA.AreaCode AS AreaCode,
				NULL AS PoliceStationID,
				MAX(TA.AreaName) AS AreaPoliceName,
				SUM(CASE U.GuildID WHEN 2 THEN 1 ELSE 0 END) AS HotelCount,
				SUM(CASE U.GuildID WHEN 3 THEN 1 ELSE 0 END) AS SchoolCount,
				SUM(CASE U.GuildID WHEN 4 THEN 1 ELSE 0 END) AS EnterpriseCount,
				SUM(CASE U.GuildID WHEN 5 THEN 1 ELSE 0 END) AS WifiUnitCount,
				SUM(CASE WHEN U.GuildID<>1 THEN 1 ELSE 0 END) AS AllUnitCount,
				SUM(CASE WHEN US.CurStatus=1 AND U.GuildID<>1 THEN 1 ELSE 0 END) AS OnlineUnit,
				SUM(CASE WHEN ISNULL(US.CurStatus,0)=0 AND U.GuildID<>1 THEN 1 ELSE 0 END) AS OfflineUnit,
				(CASE WHEN SUM(CASE WHEN U.GuildID<>1 THEN 1 ELSE 0 END)=0 THEN 0 ELSE
				SUM(CASE WHEN US.CurStatus=1 AND U.GuildID<>1 THEN 1 ELSE 0 END)*1.00/SUM(CASE WHEN U.GuildID<>1 THEN 1 ELSE 0 END) END)*100 AS UnitOnlineRate,
				MAX(ISNULL(TAC.OnlineCustomTotal,0)) AS OnlineCustomTotal,
				MAX(ISNULL(TAA.AlertCount,0)) AS AlertCount
				FROM #TempArea TA
				LEFT JOIN dbo.NSMC_tb_Unit U WITH(NOLOCK)
					ON U.AreaCode=TA.AreaCode
				LEFT JOIN dbo.NSMC_re_UnitStatus US WITH(NOLOCK)
					ON U.UnitID = US.UnitID
				LEFT JOIN #TempAreaComp TAC
					ON TA.AreaCode=TAC.AreaCode
				LEFT JOIN #TempAreaAlert TAA
					ON TAA.AreaCode = TA.AreaCode
				GROUP BY TA.AreaCode
		--再统计各警局辖区的统计值
		SELECT UPS.PoliceStationID,
			   COUNT(1) AS OnlineCustomTotal
			   INTO #TempComp
				FROM dbo.NSMC_log_Customer C WITH(NOLOCK)
				INNER JOIN dbo.NSMC_re_UnitPoliceStation UPS WITH(NOLOCK)
					ON C.UnitID = UPS.UnitID
				INNER JOIN dbo.NSMC_tb_Unit U WITH(NOLOCK)
					ON UPS.UnitID = U.UnitID
				WHERE U.GuildID<>1
				AND C.StartTime>= CONVERT(nvarchar(10),getdate(),120) 
				AND C.StartTime <= CONVERT(nvarchar(10),DATEADD(DAY,1,getdate()),120)
				GROUP BY UPS.PoliceStationID


		SELECT UPS.PoliceStationID,
			   COUNT(1) AS AlertCount
			   INTO #TempAlert
				FROM dbo.NSMC_tb_Alert A WITH(NOLOCK)
				INNER JOIN dbo.NSMC_tb_Unit U WITH(NOLOCK)
					ON A.GuildID = U.GuildID AND A.UnitCode = U.UnitCode
				INNER JOIN dbo.NSMC_re_UnitPoliceStation UPS WITH(NOLOCK)
					ON U.UnitID=UPS.UnitID
				WHERE A.GuildID<>1 
				AND A.AlertDate >= CONVERT(nvarchar(10),getdate(),120) 
				AND A.AlertDate <= CONVERT(nvarchar(10),DATEADD(DAY,1,getdate()),120)
				GROUP BY UPS.PoliceStationID

				INSERT INTO #TempResult
				SELECT  MAX(TA.AreaID) AS AreaID,
						TA.AreaCode AS AreaCode,
						PS.PoliceStationID,
						MAX(PS.PoliceStationName) AS AreaPoliceName,
						SUM(CASE U.GuildID WHEN 2 THEN 1 ELSE 0 END) AS HotelCount,
						SUM(CASE U.GuildID WHEN 3 THEN 1 ELSE 0 END) AS SchoolCount,
						SUM(CASE U.GuildID WHEN 4 THEN 1 ELSE 0 END) AS EnterpriseCount,
						SUM(CASE U.GuildID WHEN 5 THEN 1 ELSE 0 END) AS WifiUnitCount,
						SUM(CASE WHEN U.GuildID<>1 THEN 1 ELSE 0 END) AS AllUnitCount,
						SUM(CASE WHEN US.CurStatus=1 AND U.GuildID<>1 THEN 1 ELSE 0 END) AS OnlineUnit,
						SUM(CASE WHEN ISNULL(US.CurStatus,0)=0 AND U.GuildID<>1 THEN 1 ELSE 0 END) AS OfflineUnit,
						(CASE WHEN SUM(CASE WHEN U.GuildID<>1 THEN 1 ELSE 0 END)=0 THEN 0 ELSE
						SUM(CASE WHEN US.CurStatus=1 AND U.GuildID<>1 THEN 1 ELSE 0 END)*1.00/SUM(CASE WHEN U.GuildID<>1 THEN 1 ELSE 0 END) END)*100 AS UnitOnlineRate,
						MAX(ISNULL(TC.OnlineCustomTotal,0)) AS OnlineCustomTotal,
						MAX(ISNULL(TA2.AlertCount,0)) AS AlertCount
						FROM #TempArea TA
						INNER JOIN dbo.NSMC_tb_PoliceStation PS WITH(NOLOCK)
							ON TA.AreaID = PS.AreaID
						LEFT JOIN dbo.NSMC_re_UnitPoliceStation UPS WITH(NOLOCK)
							ON PS.PoliceStationID = UPS.PoliceStationID
						LEFT JOIN dbo.NSMC_tb_Unit U WITH(NOLOCK)
							ON UPS.UnitID = U.UnitID
						LEFT JOIN dbo.NSMC_re_UnitStatus US WITH(NOLOCK)
							ON U.UnitID = US.UnitID
						LEFT JOIN #TempAlert TA2
							ON PS.PoliceStationID=TA2.PoliceStationID
						LEFT JOIN #TempComp TC 
							ON PS.PoliceStationID = TC.PoliceStationID
						GROUP BY TA.AreaCode,PS.PoliceStationID

		SELECT  AreaID,
				AreaCode,
				PoliceStationID,
				AreaPoliceName ,
				HotelCount ,
				SchoolCount,
				EnterpriseCount,
				WifiUnitCount,
				AllUnitCount ,
				OnlineUnit ,
				OfflineUnit,
				UnitOnlineRate,
				OnlineCustomTotal ,
				AlertCount
				 FROM #TempResult ORDER BY AreaCode,PoliceStationID ASC
	END
写的比较好的存储过程备份
