MySql:需要一些逻辑处理的更新行

Question

给出下表:

id    | company   | names | group
-------------------------------------
0     |     1     | John  |  1
1     |     1     | Doe   |  null //populate with preceding group number (i.e. 1) 
2     |     1     | Yo    |  null //populate with preceding group number (i.e. 1) 
3     |     1     | Zoe   |  null //populate with preceding group number (i.e. 1) 
4     |     1     | Jack  |  2
5     |     1     | Doe   |  null //populate with preceding group number (i.e. 2) 
6     |     1     | Yo    |  null //populate with preceding group number (i.e. 2) 

我可以知道如何通过 sql 语句仅将空值更新为其前面的组号吗？谢谢。

Answer 1

试试这个:

UPDATE tablename t1
  JOIN (
    SELECT ID, @s:=IF(`group` IS NULL, @s, `group`) `group`
      FROM (SELECT * FROM tablename  ORDER BY ID) r,
    (SELECT @s:=NULL) t
  ) t2
  ON t1.ID = t2.ID
SET t1.`group`= t2.`group`

SQLFIDDLE DEMO