sql server 创建临时表、循环

 

需求：求表中某一字段中不同数据，在这个表中的数量
优点：减轻了 sql 语句 行转列，列转行的 繁琐 
方法：

1. 先创建新的存储过程，声明变量和函数名
2. 创建临时表，声明局部变量名   
3. 声明游标、打开游标、提取结果带入游标
4. 对游标内容进行修饰，将修饰内容代入游标，关闭游标，解除游标参照
          

USE [crm_zibogd]
GO

/****** Object:  StoredProcedure [dbo].[CSPD]    Script Date: 2018/11/24 9:17:38 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		<Author,,吴炳信>
-- Create date: <Create Date,,>
-- Description:	<Description,,派单工单统计>
-- =============================================
ALTER PROCEDURE [dbo].[CSPD]
	-- Add the parameters for the stored procedure here
	@szStart varchar(32),      ---开始时间
	@szEnd   varchar(32)       ---结束时间
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @SQL NVARCHAR(MAX)			--declare 声明局部变量
	DECLARE @CMD VARCHAR(2048)
	DECLARE @where VARCHAR(2048)
	
	DECLARE @TypeName   INT
    DECLARE @SL   INT
	DECLARE @ALL  INT
	
	 
	CREATE TABLE #TmpOrder --创建临时表#Tmp
     (
		ID        INT IDENTITY (1,1)  not null, --创建列ID,并且每次新增一条记录就会加1
        DQ_1  int,
        DQ_2  INT,
		DQ_3  INT,
		DQ_4  INT,
		DQ_5  INT,
		DQ_6  INT,
		DQ_7  INT,
		DQ_8  INT,
		DQ_9  INT,
		DQ_10  INT,
		DQ_12  INT,
		GSDQ  nvarchar(100),	
     );

	---维修派单工单统计
	---OrderType 维修-数字电视、宽带 报修  ，Brand 数字电视、宽带
	---Admissibility 客服中心
	---ComId  分公司
	---state (0-未处理，1-已预约，2-以结束，3-已退单，4-已追回)

	 INSERT INTO #TmpOrder (GSDQ) VALUES ('客服中心维修派单数')
	 INSERT INTO #TmpOrder (GSDQ) VALUES ('分公司自派维修派单数')
	 INSERT INTO #TmpOrder (GSDQ) VALUES ('客户经理人数') 
	 INSERT INTO #TmpOrder (GSDQ) VALUES ('数字电视报修数量')
	 

		 -- 声明游标 3
    DECLARE RPT_Type CURSOR FOR Select Id From Sys_Company WHERE stat=0 ORDER BY ID    --RPT_Type = Sys_Company 表中的ID
	--打开游标
	OPEN RPT_Type
	--将提取结果带入游标
	FETCH NEXT FROM RPT_Type INTO @TypeName

	WHILE @@FETCH_STATUS = 0

	BEGIN
	--对游标内容进行修饰		
								
								
		SET @sql='SELECT @SL=COUNT(ComId) 
		FROM Mobile_WorkOrder_131130 with(nolock) WHERE ComId = '+cast(@TypeName as nvarchar)+' 
		and  left (Admissibility ,4) = ''客服中心''
		and  addtime between '''+@szStart+''' and  '''+@szEnd+'''' 
		PRINT @sql
		exec  sp_executesql @sql,N'@SL int output',@SL output
		
		SET @sql='UPDATE #TmpOrder SET DQ_'+cast(@TypeName as nvarchar)+' = '+cast(@SL as nvarchar)+ ' where GSDQ =''客服中心维修派单数''' 
				--DQ_'+cast(@TypeName as nvarchar) = DQ_"ID"  | cast(@SL as nvarchar) = COUNT(Id)
		PRINT @sql
		exec  (@sql)

	--将修饰内容代入游标	
	FETCH NEXT FROM RPT_Type INTO  @TypeName
	END
	-- 关闭游标
	CLOSE RPT_Type
	--解除游标参照
	DEALLOCATE RPT_Type


		 -- 声明游标 4
    DECLARE RPT_Type CURSOR FOR Select Id From Sys_Company WHERE stat=0 ORDER BY ID    --RPT_Type = Sys_Company 表中的ID
	--打开游标
	OPEN RPT_Type
	--将提取结果带入游标
	FETCH NEXT FROM RPT_Type INTO @TypeName

	WHILE @@FETCH_STATUS = 0

	BEGIN
	--对游标内容进行修饰		
								
								
		SET @sql='SELECT @SL=COUNT(ComId) 
		FROM Mobile_WorkOrder_131130 with(nolock) WHERE ComId = '+cast(@TypeName as nvarchar)+' 
		and left (WorkId,3)=''601''
		and  addtime between '''+@szStart+''' and  '''+@szEnd+'''' 
		PRINT @sql
		exec  sp_executesql @sql,N'@SL int output',@SL output
		
		SET @sql='UPDATE #TmpOrder SET DQ_'+cast(@TypeName as nvarchar)+' = '+cast(@SL as nvarchar)+ ' where GSDQ =''分公司自派维修派单数''' 
				--DQ_'+cast(@TypeName as nvarchar) = DQ_"ID"  | cast(@SL as nvarchar) = COUNT(Id)
		PRINT @sql
		exec  (@sql)

	--将修饰内容代入游标	
	FETCH NEXT FROM RPT_Type INTO  @TypeName
	END
	-- 关闭游标
	CLOSE RPT_Type
	--解除游标参照
	DEALLOCATE RPT_Type


		 -- 声明游标  5
    DECLARE RPT_Type CURSOR FOR Select Id From Sys_Company WHERE stat=0 ORDER BY ID    --RPT_Type = Sys_Company 表中的ID
	--打开游标
	OPEN RPT_Type
	--将提取结果带入游标
	FETCH NEXT FROM RPT_Type INTO @TypeName

	WHILE @@FETCH_STATUS = 0

	BEGIN
	--对游标内容进行修饰		
								
		SET @sql='SELECT @SL=COUNT(ComId) 
		FROM Mobile_Users with(nolock) WHERE ComId = '+cast(@TypeName as nvarchar)+' 
		and AgentRole=4 and AgentStat=1
		' 
		PRINT @sql
		exec  sp_executesql @sql,N'@SL int output',@SL output
		
		SET @sql='UPDATE #TmpOrder SET DQ_'+cast(@TypeName as nvarchar)+' = '+cast(@SL as nvarchar)+ ' where GSDQ =''客户经理人数''' 
				--DQ_'+cast(@TypeName as nvarchar) = DQ_"ID"  | cast(@SL as nvarchar) = COUNT(Id)
		PRINT @sql
		exec  (@sql)

	--将修饰内容代入游标	
	FETCH NEXT FROM RPT_Type INTO  @TypeName
	END
	-- 关闭游标
	CLOSE RPT_Type
	--解除游标参照
	DEALLOCATE RPT_Type


		 -- 声明游标  6
    DECLARE RPT_Type CURSOR FOR Select Id From Sys_Company WHERE stat=0 ORDER BY ID    --RPT_Type = Sys_Company 表中的ID
	--打开游标
	OPEN RPT_Type
	--将提取结果带入游标
	FETCH NEXT FROM RPT_Type INTO @TypeName

	WHILE @@FETCH_STATUS = 0

	BEGIN
	--对游标内容进行修饰		
								
		SET @sql='SELECT @SL=COUNT(ComId) 
		FROM Mobile_WorkOrder_131130 with(nolock) WHERE ComId = '+cast(@TypeName as nvarchar)+' 
		and left (Brand,4)= ''数字电视''	
		and  addtime between '''+@szStart+''' and  '''+@szEnd+'''' 
		PRINT @sql
		exec  sp_executesql @sql,N'@SL int output',@SL output
		
		SET @sql='UPDATE #TmpOrder SET DQ_'+cast(@TypeName as nvarchar)+' = '+cast(@SL as nvarchar)+ ' where GSDQ =''数字电视报修数量''' 
				--DQ_'+cast(@TypeName as nvarchar) = DQ_"ID"  | cast(@SL as nvarchar) = COUNT(Id)
		PRINT @sql
		exec  (@sql)

	--将修饰内容代入游标	
	FETCH NEXT FROM RPT_Type INTO  @TypeName
	END
	-- 关闭游标
	CLOSE RPT_Type
	--解除游标参照
	DEALLOCATE RPT_Type

    -- Insert statements for procedure here
	SELECT * From #TmpOrder
END

GO

查询 是否正确： exec [dbo].[CSPD] ‘时间段’，‘时间段’