一个查询中的mysql列表顺序更新(上移，下移)

Question

如果我只知道一个 id，有没有办法在一个查询中向上/向下移动列表项

像这样的

update tbl 
   SET list_order = 
    CASE 
     WHEN id = 10  
        THEN (SELECT list_order FROM tbl 
               WHERE list_order > (SELECT list_order FROM tbl WHERE id = 10)
               ORDER BY list_order ASC LIMIT 1)
     WHEN id = (SELECT id FROM tbl 
                 WHERE list_order > (SELECT list_order FROM tbl WHERE id = 10) 
                 ORDER BY list_order ASC LIMIT 1)
        THEN (SELECT list_order FROM tbl WHERE id = 10) 
     ELSE list_order
    END

待定

id  |  list_order
-----------------
 10 |   1    
 22 |   2
 33 |   3

到>

id  |  list_order
-----------------
 22 |   1    
 10 |   2
 33 |   3

我对这个问题的回答(例如 MOVE UP in list row with ID 10)

    UPDATE tbl e
     JOIN (SELECT id, list_order FROM tbl 
          WHERE list_order < (SELECT list_order FROM tbl WHERE id = 10)
                 ORDER BY list_order DESC LIMIT 1) t1
     JOIN (SELECT list_order FROM tbl WHERE id = 10) t2
     SET e.list_order = CASE 
      WHEN e.id = 10  
        THEN t1.list_order 
      WHEN e.id = t1.id
        THEN t2.list_order 
      ELSE e.list_order
     END 

Answer 1

如果将 id 22 向上移动一位 -

UPDATE tbl t1
INNER JOIN tbl t2
    ON t1.list_order - 1 = t2.listorder
SET
    t1.list_order = t1.list_order - 1,
    t2.list_order = t2.list_order + 1
WHERE t1.id = 22;