排序表，在MySQL中使用带有SELECT的循环和带有NATURAL ORDER的UPDATE [英] Sort table, using loop in MySQL with SELECT and UPDATE with NATURAL ORDER

问题描述

很久以前，我问过 HERE 帮助对表格进行排序.用户"piotrm"给予​​了出色的解决，这要归功于他！效果很好！

Some long time ago I asked HERE to help with sorting the table. And user "piotrm" gave an exellent sollution, thanks to him! It works perfect!

SELECT @p:=0, @parent:=0;
UPDATE page p1
JOIN
( SELECT title, 
  CASE WHEN @parent<>parent_id THEN @p:=0 ELSE @p:=@p+1 END as position,
  @parent:=parent_id as parent_id
  FROM page
  ORDER BY parent_id, title DESC ) p2
ON p1.title = p2.title AND p1.parent_id = p2.parent_id
SET p1.position = p2.position

现在，我需要使用NATURAL SORTING升级此代码.我的意思是仍然需要以同样的方式进行排序，但是当它遇到以"2"，"13"，"1"，"26"开头的标题时，就必须将其排序为"1"，"13"，"2'，'26'；但是'1'，'2'，'13'，'26'.

Now I need this code to be upgraded with NATURAL SORTING. I mean it STILL needs to be sorted THE SAME WAY, but when it meets TITLE STARTING WITH '2', '13', '1', '26', it has to be ordered NOT '1', '13', '2', '26'; BUT '1', '2', '13', '26'.

我很抱歉缺乏SQL知识，但我真的需要您的帮助！非常感谢！

I apologize for lack of knowledge in SQL, but I really need your help! Thank you a lot!

推荐答案

SELECT @p:=0, @parent:=0;
UPDATE page p1
JOIN
( SELECT title, 
  CASE WHEN @parent<>parent_id THEN @p:=0 ELSE @p:=@p+1 END as position,
  @parent:=parent_id as parent_id
  FROM page
  ORDER BY LENGTH(title), title, parent_id ASC ) p2
ON p1.title = p2.title AND p1.parent_id = p2.parent_id
SET p1.position = p2.position

这篇关于排序表，在MySQL中使用带有SELECT的循环和带有NATURAL ORDER的UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！