根据最近时间求单价....SQL

CREATE TABLE tb(日期 datetime, 料号 NVARCHAR(30), 单价 int)

INSERT tb   SELECT '2008/5/1','AAA',2
UNION  ALL   SELECT '2008/6/6','BBB',22
UNION  ALL    SELECT '2008/6/6','CCC ',12
UNION  ALL   SELECT '2008/7/3','BBB',21
UNION  ALL    SELECT '2008/7/9','AAA  ',1

--select * from tb

--select distinct 料号 FROM TB
--select count (distinct 料号) from tb

--select TOP 1 日期 ,单价 from tb where 料号 ='AAA' order by 日期 DESC

create procedure sp_查询最近单价
WITH ENCRYPTION
AS
begin tran--启动事务
        declare @ERROR int
        set @ERROR=0
        begin
            declare @temp table
             (
               [日期] datetime,
               [料号] NVARCHAR(30),
               [单价] int
              )
              declare @tempdb料号 table
             (
                [id] int identity(1,1),
                [料号] NVARCHAR(30)
              )
             declare @改后temp table
             (
               [日期] datetime,
               [料号] NVARCHAR(30),
               [单价] int
              )
             insert into @temp select *  from tb
              -- SELECT * FROM @tempdb
             SET @ERROR =@ERROR +@@ERROR
                          IF (@ERROR <>0) GOTO EXT
             insert into @tempdb料号 select DISTINCT 料号  from tb
             -- SELECT * FROM @tempdb料号
             SET @ERROR =@ERROR +@@ERROR
                         IF (@ERROR <>0) GOTO EXT
             
           
             declare @temp日期 datetime,@temp料号 varchar(30),@temp单价 INT,@I int
             set @i=1
             select distinct 料号 FROM @tempdb料号  where   id=@i   
            
             WHILE @@rowcount<>0   
                 begin
                     select TOP 1 @temp日期=日期 ,@temp料号=料号,@temp单价=单价 from @temp where 料号 =(select distinct 料号 FROM @tempdb料号  where   id=@i )
                     order by 日期 DESC
                     SET @ERROR =@ERROR +@@ERROR
                          IF (@ERROR <>0) GOTO EXT
                       INSERT INTO @改后temp VALUES(@temp日期,@temp料号,@temp单价)
                     SET @ERROR =@ERROR +@@ERROR
                         IF (@ERROR <>0) GOTO EXT
                     set   @i=@i+1  
                  select distinct 料号 FROM @tempdb料号  where   id=@i   
                  end
       
             select * from @改后temp
            
        end

  --异常出口
   EXT:
    
   --判断执行状态
  IF (@ERROR =0)
     BEGIN
         COMMIT
     END
  ELSE
     ROLLBACK

exec sp_查询最近单价

结果:
2008-07-09 00:00:00.000    AAA      1
2008-07-03 00:00:00.000    BBB    21
2008-06-06 00:00:00.000    CCC     12