将Mysql查询的结果更新到本表的某字段下

Question

我使用下面的代码查询出了一张表结构如下

SELECT xh,xm,bj,jd,rank FROM
    (SELECT *,@rownum:=@rownum+1 AS rownum,IF(@pa=ff.bj,@rank:=@rank+1,@rank:=1) AS rank,@pa:=ff.bj
    FROM
       (SELECT xh,xm,jd,bj FROM totaltable GROUP BY bj,jd ORDER BY bj ASC, jd DESC) ff,(SELECT @rank:=0,@rownum:=0,@pa=NULL) tt) result

我想将rank 字段更新到totaltable表的对应的学号（xh）的某字段(bpm)下默认为空 应该如何写sql语句

Answer 1

update totaltable
    join (SELECT xh,xm,bj,jd,rank FROM (SELECT *,@rownum:=@rownum+1 AS rownum,IF(@pa=ff.bj,@rank:=@rank+1,@rank:=1) AS rank,@pa:=ff.bj
        FROM
           (SELECT xh,xm,jd,bj FROM totaltable GROUP BY bj,jd ORDER BY bj ASC, jd DESC) ff,(SELECT @rank:=0,@rownum:=0,@pa=NULL) tt) result) tmp on totaltable.xh = tmp.xh
set totaltable.bpm = tmp.rank

你试试这个呢，update语句去join你『select结果』临时表，希望能帮到你。