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

问题描述

请帮助我在MySQL中对表格进行排序.我只需要制作一次，而不是用于脚本或动态的其他地方.

Please help me to sort my table in MySQL. I need to make it only once, not for use in scripts or somewhere else dynamically.

这是我的表格页面":

|  ...  |  title  |  ...  |  parent_id  |  ...  |  position  |  ...  |

我需要使用以下规则更新表:选择具有相同parent_id的所有行，按title DESC对其进行排序，并设置第一行position = 0，第二行= 1，依此类推.然后为所有循环parent_id.

I need to update table with the rule: select all rows with the same parent_id, sort them by title DESC and set first row position = 0, second = 1 etc. And loop this for all parent_id.

我进行了查询以显示需要的内容，但没有UPDATE且仅针对一个parent_id.但是由于有成千上万个parent_id，此查询需要循环.选择后，必须将position更新为与当前行的@number相同的值.

I made a query to display what I need, but with no UPDATE and only for one parent_id. But as there are some thousands parent_id this query needs a loop. And after selecting, position must be updated to have the same value as @number for current row.

SET @number = -1;
SELECT @number:=@number+1 AS number, p.* FROM `page` AS p WHERE parent_id=1
  ORDER BY title DESC;

感谢您的帮助！

推荐答案

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

如果您的(parent_id，title)对不是唯一的，请使用主键作为连接条件-您需要添加它才能在括号中进行选择.

If your (parent_id,title) pairs aren't unique use your primary key as join condition - you will need to add it to select in parenthesis.

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