mysql - 通过重新排序表来更新位置字段

Question

我正在尝试重新排序表格。
此表包含产品的属性列表。我想按字母顺序重新排列所有“颜色”属性 (id_attribute_group = 2)(白色和黑色除外)。这个查询给了我想要的结果:

SELECT a.`id_attribute`, a.`id_attribute_group`, a.`color`, (@count := @count + 1) AS position
FROM `ps_attribute_lang` al, `ps_attribute` a
CROSS JOIN (SELECT @count := 0) tcount 
WHERE a.`id_attribute` = al.`id_attribute`
AND a.`id_attribute_group` = 2
ORDER BY
CASE
    WHEN  al.`name` LIKE '% - white' THEN 1
    WHEN al.`name` LIKE '% - black' THEN 2
    ELSE SUBSTRING(al.`name`, LOCATE(' - ', al.`name`)+3) #prefix for color
END 
ASC

产生类似的东西

+==============+====================+=========+==========+
| id_attribute | id_attribute_group | color   | position |
+==============+====================+=========+==========+
| 12           | 2                  | #FFFFFF | 1        | // WHITE
+--------------+--------------------+---------+----------+
| 45           | 2                  | #FEFEFE | 2        | // WHITE
+--------------+--------------------+---------+----------+
| 25           | 2                  | #000000 | 3        | // BLACK
+--------------+--------------------+---------+----------+
| 1112         | 2                  | #010101 | 4        | // BLACK
+--------------+--------------------+---------+----------+
| 120          | 2                  | #57585C | 5        | // ANTHRACITE
+--------------+--------------------+---------+----------+
| ...          | ...                | ...     | ...      | // ANTHRACITE
+--------------+--------------------+---------+----------+
| 6            | 2                  | #4D5166 | 1150     | // ZINC
+--------------+--------------------+---------+----------+

太棒了!现在我只想REPLACE条目:

REPLACE INTO `ps_attribute` 
[the previous query]

现在它按 id 对所有内容进行排序，但确实改变了位置:

+==============+====================+=========+==========+
| id_attribute | id_attribute_group | color   | position |
+==============+====================+=========+==========+
| 1            | 2                  | #ab4894 | 1        | 
+--------------+--------------------+---------+----------+
| 2            | 2                  | #ba4158 | 2        | 
+--------------+--------------------+---------+----------+
| 3            | 2                  | #016873 | 3        | 
+--------------+--------------------+---------+----------+
| 4            | 2                  | #894657 | 4        | 
+--------------+--------------------+---------+----------+
| 5            | 2                  | #fa4891 | 5        | 
+--------------+--------------------+---------+----------+
| ...          | ...                | ...     | ...      | 
+--------------+--------------------+---------+----------+
| 1150         | 2                  | #4894af | 1150     | 
+--------------+--------------------+---------+----------+

我错过了什么？我还尝试制作一个 TEMPORARY 表，但它也不起作用。如果我用

显示它

SELECT * FROM `temp` ORDER BY `position` 

我得到与上面相同的结果。我还尝试使用 INSERT INTO ... ON DUPLICATE KEY UPDATE

Answer 1

也许您可以ALTER 表格:

ALTER TABLE
    ps_attribute_lang as al
ORDER BY
CASE
    WHEN  al.`name` LIKE '% - white' THEN 1
    WHEN al.`name` LIKE '% - black' THEN 2
    ELSE SUBSTRING(al.`name`, LOCATE(' - ', al.`name`)+3) #prefix for color
END 
ASC

编辑

尝试以这种方式重新编号:

    SELECT result.*, (@count := @count + 1) AS position
FROM
(SELECT a.`id_attribute`, a.`id_attribute_group`, a.`color`, al.`name`
FROM `ps_attribute_lang` al, `ps_attribute` a
WHERE a.`id_attribute` = al.`id_attribute`
AND a.`id_attribute_group` = 2
AND al.`id_lang` = 1
ORDER BY
CASE
    WHEN  al.`name` LIKE '% - white' THEN 1
    WHEN al.`name` LIKE '% - black' THEN 2
    ELSE SUBSTRING(al.`name`, LOCATE(' - ', al.`name`)+3) #prefix for color
END 
ASC) as result
CROSS JOIN (SELECT @count := 0) tcount