使用游标对查询结果循环代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】使用游标对查询结果循环
作者:mcgtts / 发布于2013/12/31/ 1006
1set ANSI_NULLS ON

 3set QUOTED_IDENTIFIER ON

 5GO

 7ALTER PROCEDURE [dbo].[GetProductPriceFromYear]

   @ProductID int,

   @Date Datetime

13AS

15BEGIN

   create table #temp1

19(

   id int IDENTITY(1,1) NOT NULL primary key,

   AddDate varchar(20),

   [datename] varchar(20),

   week varchar(20),

   ProductName varchar(20),

   ProductID varchar(20),

   LPrice float(20),

   HPrice float(20),

   APrice float(20),

   WeekLPrice float(20),

   WeekHPrice float(20),

   WeekAPrice float(20),

   WeekDayCount int

47)



51declare @tempDate varchar(20)

53declare @AddDate varchar(20)

55declare @datenam varchar(20)

57declare @week varchar(20)

59declare @ProductName varchar(20)

61declare @Product varchar(20)

63declare @LPrice varchar(20)

65declare @HPrice varchar(20)

67declare @APrice varchar(20)

69Declare  CurPrice  Cursor  For

      select distinct AddDate=CONVERT(char(10),a.AddDate,20),datename(dw,a.AddDate) as [datename],

73datepart(ww,a.AddDate) as week, b.ProductName,a.ProductID,a.LPrice,a.HPrice,a.APrice

75from dbo.SmsPrice as a join dbo.SmsProduct as b

77on a.ProductID = b.ProductId where a.ProductID = @ProductID AND Datediff(yy,a.AddDate,@Date)=0 order by AddDate desc

79Open  CurPrice  

81Fetch from CurPrice into @AddDate,@datenam,@week,@ProductName,@Product,@LPrice,@HPrice,@APrice

83insert into #temp1(AddDate,[datename],week,ProductName,ProductID,LPrice,HPrice,APrice)

          values(@AddDate,@datenam,@week,@ProductName,@Product,@LPrice,@HPrice,@APrice)



89While  @@FETCH_STATUS=0 

 BEGIN

      

      if(@tempDate!=@AddDate)

          insert into #temp1(AddDate,[datename],week,ProductName,ProductID,LPrice,HPrice,APrice)

          values(@AddDate,@datenam,@week,@ProductName,@Product,@LPrice,@HPrice,@APrice)

      set @tempDate = @AddDate

      Fetch from CurPrice into @AddDate,@datenam,@week,@ProductName,@Product,@LPrice,@HPrice,@APrice

   end

Close  CurPrice

Deallocate  CurPrice



113--insert into #temp1(AddDate,[datename],week,ProductName,ProductID,LPrice,HPrice,APrice)

115--select distinct AddDate=CONVERT(char(10),a.AddDate,20),datename(dw,a.AddDate) as [datename],

117--datepart(ww,a.AddDate) as week, b.ProductName,a.ProductID,a.LPrice,a.HPrice,a.APrice

119--from dbo.Price as a join dbo.SmsProduct as b

121--on a.ProductID = b.ProductId where a.ProductID = @ProductID AND Datediff(yy,a.AddDate,@Date)=0 order by AddDate desc

123declare @weekid varchar(20)

125declare @tempWeek2 varchar(20)

127declare @WeekLPrice float(20)

129declare @WeekHPrice float(20)

131declare @WeekAPrice float(20)



135Declare  Cur  Cursor  For

      select distinct week from #temp1

139Open  Cur  

Fetch  Cur  Into  @weekid

143While  @@FETCH_STATUS=0 

   BEGIN

          select @tempWeek2=week,@WeekLPrice=avg(CONVERT(float,LPrice)),@WeekHPrice=avg(CONVERT(float,HPrice)),

             @WeekAPrice=avg(CONVERT(float,APrice)) from #temp1 where week =@weekid group by week

          update #temp1 set WeekLPrice = @WeekLPrice,WeekHPrice=@WeekHPrice,WeekAPrice=@WeekAPrice,

             WeekDayCount=(select count(*) from #temp1 where week = @weekid) where week = @weekid

          Fetch  Cur  Into  @weekid

      end

Close  Cur 

Deallocate  cur

163select * from #temp1

165END

166
试试其它关键字
　游标循环　
同语言下
可能有用的
mcgtts贡献的其它代码(6)