sql语句的循环执行

最新推荐文章于 2025-03-14 01:05:07 发布

转载最新推荐文章于 2025-03-14 01:05:07 发布 · 200 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/lucoo/archive/2012/12/29/2839102.html

文章标签：

#数据库 #开发工具

本文介绍了一个用于计算社区业主信息数量的存储过程，通过参数年份过滤数据，按月份进行计数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

USE [CommunityApp]
GO
/****** Object:  StoredProcedure [dbo].[sp_count_OwnerInfo]    Script Date: 02/24/2016 17:08:51 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_count_OwnerInfo]  
 @year  varchar(30)
AS
  declare @i int,
  @CommunityID int,
  @CommunityName nvarchar(50),
  @yearCount  int  ,
  @month1Count  int  ,
  @month2Count  int  ,
  @month3Count  int  ,
  @month4Count  int  ,
  @month5Count  int  ,
  @month6Count  int  ,
  @month7Count  int  ,
  @month8Count  int  ,
  @month9Count  int  ,
  @month10Count  int  ,
  @month11Count  int  ,
  @month12Count  int 
  
 DECLARE @tCommunity TABLE  
 (  
  CommunityID  int  ,
  CommunityName nvarchar(50)
  ,FlagID   TINYINT    
 ) 
 
 DECLARE @tcount TABLE  
 (  
  CommunityID  int  ,
  CommunityName nvarchar(50),
  yearCount  int  ,
  month1Count  int  ,
  month2Count  int  ,
  month3Count  int  ,
  month4Count  int  ,
  month5Count  int  ,
  month6Count  int  ,
  month7Count  int  ,
  month8Count  int  ,
  month9Count  int  ,
  month10Count  int  ,
  month11Count  int  ,
  month12Count  int 
 ) 
 
BEGIN  
 insert @tCommunity  select CommunityID,CommunityName,0 from CommunityBase.dbo.CommunityList
 SET @i=1  
 WHILE( @i>=1)  
 BEGIN  
    
  set @CommunityID=''  
   set @CommunityName=''  
  SELECT TOP 1 @CommunityID = CommunityID,@CommunityName = CommunityName FROM @tCommunity  WHERE flagID=0  
  SET @i=@@ROWCOUNT  
    
  IF @i<=0 GOTO Return_Lab  
     
    SELECT @yearCount=count(*) FROM OwnerInfo WHERE datediff(year,CreateTime,@year+'-1-1')=0 and CommunityID= @CommunityID  --年
    SELECT @month1Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+'-1-1')=0 and CommunityID= @CommunityID  --1月
    SELECT @month2Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+'-2-1')=0 and CommunityID= @CommunityID  --2月
    SELECT @month3Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+'-3-1')=0 and CommunityID= @CommunityID  --3月
    SELECT @month4Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+'-4-1')=0 and CommunityID= @CommunityID  --4月
    SELECT @month5Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+'-5-1')=0 and CommunityID= @CommunityID  --5月
    SELECT @month6Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+'-6-1')=0 and CommunityID= @CommunityID  --6月
    SELECT @month7Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+'-7-1')=0 and CommunityID= @CommunityID  --7月
    SELECT @month8Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+'-8-1')=0 and CommunityID= @CommunityID  --8月
    SELECT @month9Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+'-9-1')=0  and CommunityID= @CommunityID --9月
    SELECT @month10Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+'-10-1')=0 and CommunityID= @CommunityID  --10月
    SELECT @month11Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+'-11-1')=0 and CommunityID= @CommunityID  --11月
    SELECT @month12Count=count(*) FROM OwnerInfo WHERE datediff(month,CreateTime,@year+'-12-1')=0 and CommunityID= @CommunityID  --12月
   
   insert @tcount values(@CommunityID,@CommunityName,@yearCount,@month1Count,@month2Count,@month3Count,@month4Count,@month5Count,@month6Count,@month7Count,@month8Count,@month9Count,@month10Count,@month11Count,@month12Count) 
   
   
   IF @@error=0   
      UPDATE @tCommunity SET flagID=1 WHERE CommunityID = @CommunityID
       
 Return_Lab:  
 END 
 select * from   @tcount
End

转载于:https://www.cnblogs.com/lucoo/archive/2012/12/29/2839102.html