问题描述
给原有的数据新增一个字段sort,并要求原有的数据按不同的组进行排序,如图
解决方案:
SET @num := 0;
SET @class_id := NULL;
UPDATE `table_name` t1
JOIN (
SELECT
`id`,
`classes_project_info_id`,
(@sort := IF(@class_id = `classes_project_info_id`, @sort + 1, 1)) AS sort,
(@class_id := `classes_project_info_id`)
FROM `table_name`
ORDER BY `classes_project_info_id`, `id`
) t2 ON t1.`id` = t2.`id`
SET t1.`sort` = t2.sort;
码海无涯,回头是岸。