SQL 循环查询！解决办法

SQL 循环查询！~

SQL code

CREATE TABLE dbo.Employees(  Name   VarChar(40)   NOT NULL,  Date   VarChar(8)   NOT NULL,  Number Decimal(20,4) NOT NULL,);Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20120524', '50.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20120408', '60.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20120301', '40.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20120201', '20.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20120114', '10.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20111208', '50.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20111105', '30.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20111006', '40.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20110908', '90.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20110805', '50.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20110705', '510.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20110605', '120.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20110505', '100.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20110405', '210.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20110305', '120.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20110205', '30.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20110105', '50.00');Declare @Type Int    Set @Type = 0while(@Type < 9)BeginSelect A.Name      ,B.Date      ,B.Number/C.Number-1  From Employees A   Join Employees B  On A.Name = B.Name  Join Employees C  On A.Name = C.Name Where A.Date = (Select Max(Date)                   From Employees                  Where Name = B.Name                    And Date <= GetDate())   And B.Date = (Select Max(Date)                   From Employees                  Where Name = B.Name                    And Date <= Convert(VarChar(8),DateAdd(MM,-@Type,A.Date),112))   And C.Date = (Select Max(Date)                   From Employees                  Where Name = B.Name                    And Date <= Convert(VarChar(8),DateAdd(MM,-(@Type+1),A.Date),112)) Set @Type = @Type + 1End  算法：1.查询最近日期的数据2.根据最新日期去过去10个月每个月的回报数据Ps：不能使用 CREATE、Into 等关键字谢谢！~如何把我查询的结果整合在一张表中显示--Drop Table Employees

SQL code

CREATE TABLE dbo.Employees(  Name   VarChar(40)   NOT NULL,  Date   VarChar(8)   NOT NULL,  Number Decimal(20,4) NOT NULL,);Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20120524', '50.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20120408', '60.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20120301', '40.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20120201', '20.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20120114', '10.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20111208', '50.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20111105', '30.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20111006', '40.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20110908', '90.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20110805', '50.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20110705', '510.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20110605', '120.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20110505', '100.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20110405', '210.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20110305', '120.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20110205', '30.00');Insert Into dbo.Employees(Name, Date, Number)  Values('张三','20110105', '50.00');Declare @Type Int    Set @Type = 0Declare @sql varchar(max)while(@Type < 9)Begin    set @sql=isnull(@sql+' union all ','')+'Select A.Name      ,B.Date      ,B.Number/C.Number-1  From Employees A   Join Employees B  On A.Name = B.Name  Join Employees C  On A.Name = C.Name Where A.Date = (Select Max(Date)                   From Employees                  Where Name = B.Name                    And Date <= GetDate())   And B.Date = (Select Max(Date)                   From Employees                  Where Name = B.Name                    And Date <= Convert(VarChar(8),DateAdd(MM,-'+ltrim(@Type)+',A.Date),112))   And C.Date = (Select Max(Date)                   From Employees                  Where Name = B.Name                    And Date <= Convert(VarChar(8),DateAdd(MM,-('+ltrim(@Type)+'+1),A.Date),112))' Set @Type = @Type + 1End  exec(@sql) 
 
[解决办法]
SQL code
select top 10 a.name,a.date,case when b.Number=0 then 0 else a.Number/b.Number-1 end 环比 from (select *,ROW_NUMBER() over(partition by name order by date) as id from Employees) a join (select *,ROW_NUMBER() over(partition by name order by date) as id from Employees) bon a.name=b.name and a.id=b.id+1order by a.id desc