mysql - 如何使用 ORDER BY 更新这条语句

Question

我有这组数据。

id | fullname | company | assign_num
1    sample2   company1   0000-1111
2    sample3   company2   0000-1111
3    sample4   company2   0000-1111
4    sample1   company1   0000-1111
5    sample3   company1   0000-1111

assign_num 已给出。

并根据公司(例如公司 1)更新 assign_num 前任。 0000-1112, 0000-1501, 0000-1120

结果是这样的:

id | fullname | company | assign_num
1    sample2   company1   0000-1112
2    sample3   company2   0000-1111
3    sample4   company2   0000-1111
4    sample1   company1   0000-1501
5    sample3   company1   0000-1120

使用此代码它有效。

UPDATE myTable
JOIN (
  SELECT m1.id, COUNT(m2.id)+1 ord FROM myTable m1
  LEFT JOIN myTable m2 ON m1.id > m2.id AND m1.company = m2.company 
  GROUP BY m1.id
) z1 ON myTable.id = z1.id
JOIN (
  SELECT 1 ord, '0000-1112' assign_num UNION ALL
  SELECT 2 ord, '0000-1501' assign_num UNION ALL
  SELECT 3 ord, '0000-1120' assign_num
) z2
  ON z1.ord = z2.ord
SET myTable.assign_num = z2.assign_num
WHERE myTable.company = 'company1'

但我希望输出看起来像这样:(按全名 ASC 排序)

id | fullname | company | assign_num
1    sample1   company1   0000-1112
2    sample3   company2   0000-1111
3    sample4   company2   0000-1111
4    sample2   company1   0000-1501
5    sample3   company1   0000-1120

Answer 1

SQLFiddle:http://sqlfiddle.com/#!9/b33d13/1

UPDATE myTable
JOIN (
  SELECT m1.id, 
        IF(m1.company = @company, @rank := @rank + 1, @rank := 1) as ord,
        @company := m1.company
  FROM (SELECT * FROM myTable ORDER BY company, fullname) as m1 
    CROSS JOIN (SELECT @company := NULL, @rank := 0) param
) z1
  ON myTable.id = z1.id
JOIN (
  SELECT 1 ord, '0000-1112' assign_num UNION ALL
  SELECT 2 ord, '0000-1501' assign_num UNION ALL
  SELECT 3 ord, '0000-1120' assign_num
) z2
  ON z1.ord = z2.ord
SET myTable.assign_num = z2.assign_num
WHERE myTable.company = 'company1';