--t_log表的自增加属性,我去掉了.分两步,第一update,第二insert
CREATE TABLE [dbo].[T_info] ( [id] [int] IDENTITY (1, 1) NOT NULL , [classid] [int] NULL , [name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [results] [float] NULL , [stime] [datetime] NULL ) ON [PRIMARY] 
GO 
CREATE TABLE [dbo].[T_log] ( [id] [int] , [name] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL , [vm] [numeric](18, 0) NULL ) ON [PRIMARY] 
GO 
insert into T_info(classid,name,results,stime) values('1','张三','90','2007-12-27') 
insert into T_info(classid,name,results,stime) values('1','AA','80','2007-12-27') 
insert into T_info(classid,name,results,stime) values('1','BB','70','2007-12-27') 
insert into T_info(classid,name,results,stime) values('1','CC','60','2007-12-27') 
insert into T_info(classid,name,results,stime) values('2','DD','50','2007-12-27') 
insert into T_info(classid,name,results,stime) values('2','EE','40','2007-12-27') 
insert into T_info(classid,name,results,stime) values('2','FF','30','2007-12-27') 
insert into T_info(classid,name,results,stime) values('2','GG','65','2007-12-27') 
insert into T_info(classid,name,results,stime) values('1','张三','90','2007-12-27') 
insert into T_info(classid,name,results,stime) values('3','AA2','80','2007-12-27') 
insert into T_info(classid,name,results,stime) values('3','BB3','70','2007-12-27') 
insert into T_info(classid,name,results,stime) values('3','CC4','60','2007-12-27') 
insert into T_info(classid,name,results,stime) values('1','DD5','50','2007-12-27') 
insert into T_info(classid,name,results,stime) values('4','EE6','40','2007-12-27') 
insert into T_info(classid,name,results,stime) values('4','FF4','30','2007-12-27') 
insert into T_info(classid,name,results,stime) values('4','GG5','65','2007-12-27') 
insert into T_log(id , name,vm) values(1 , '张三','109')
go

declare @stime as datetime
set @stime = '2007-12-27'

--update 
update t_log
set vm = m.vm
from t_log t , (select id,name ,vm = case when px = 1 then 1200
                 when px = 2 then 1000
                 when px = 3 then 850
            end
from 
(
  select px = (select count(1) from t_info where classid = t.classid and stime = @stime and results > t.results) + 1,* from t_info t where stime = @stime
) t
where px <= 3) m
where t.name = m.name

--insert
insert into t_log 
select id,name ,vm = case when px = 1 then 1200
                 when px = 2 then 1000
                 when px = 3 then 850
            end
from 
(
  select px = (select c