想根据一个结果集来更新表中的字段，也就是这个意思：

update a set a.s=b.ss,a.f=b.ff where id in (select id from b where xxx)

我的第一想法是使用游标。结果发现游标的效率太低了。经高人指点使用了临时表

代码：

 1 

declare

 

@t

 

table

(userid 

int

,username 

nvarchar

(

50

),commenttime 

datetime

,questionid 

nvarchar

(

50

))

 2 

 3 

insert

 

@t

(userid,username,commenttime,questionid)

 4 

select

 userid,username,commenttime,questionid 

from

 comment c

 5 

where

 questionid 

in

(

 6 

select

 questionId 

from

  question

 7 

where

 CreateTime

<

DATEADD

(minute,

1440

,

getdate

())

 8 

and

 IsAutoAdd

=

100

 

and

 questionstatus

<>

1

)

 9 

and

 

not

   

exists

10 

(

select

 

1

 

from

 comment 

where

 questionid

=

c.questionid 

and

 commenttime

>

 c.commenttime);

11 

12 

13 

    

update

 question 

set

 lastcommentuserID

=

t.userid,

14 

                        lastcommentuserName

=

t.username,

15 

                        LastCommentTime

=

t.commenttime

16 

                    

from

 

@t

 t

17 

                        

where

 question.questionID

=t.questionid

18 --set @error=@error+@@error;