MySQL分组修改排序序号

update jxgz_rymc r set Reorder = ( select i from (
SELECT
      (
          @i := CASE
          WHEN @DepartmentId = t1.DepartmentId THEN
             @i + 1
          ELSE
              1
          END
      ) AS i,
     (@DepartmentId := t1.DepartmentId),
     t1.PersonDirectoryId
 FROM
     jxgz_rymc t1,
     (
         SELECT
             @i := 0 ,@DepartmentId := ‘‘
     ) AS t2
 ORDER BY
     t1.DepartmentId,
     t1.PersonName,
     t1.PersonDirectoryId
) a where r.PersonDirectoryId = a.PersonDirectoryId);

相关推荐