如何以特定方式对mysql表进行排序

Question

由于查询结果，我有下表：

    SELECT f.id, f.parent_id, f.name, f.fullpath, 
    FROM folders f
    WHERE fullpath LIKE CONCAT("%", 'fs-3', "%") 
    ORDER BY fullpath ASC, name ASC;   

id      parent_id       name        fullpath
-----------------------------------------------------------------
fs-3    null            root        fs-3
fs-d    fs-3            test        fs-3/fs-d
fs-5    fs-d            test        fs-3/fs-d/fs-5
fs-g    fs-3            test3       fs-3/fs-g
fs-2    fs-g            test        fs-3/fs-g/fs-2
fs-s    fs-2            test        fs-3/fs-g/fs-2/fs-s
fs-y    fs-3            test2       fs-3/fs-y
fs-4    fs-y            test        fs-3/fs-y/fs-4

这个表是按

ORDER BY fullpath ASC, name ASC

希望得到的结果是，name column按test3在test2之后排序的方式排序，同时保持fullpath排序。但我不知道怎么做到这一点？

id      parent_id       name        fullpath
-----------------------------------------------------------------
fs-3    null            root        fs-3
fs-d    fs-3            test        fs-3/fs-d
fs-5    fs-d            test        fs-3/fs-d/fs-5
fs-y    fs-3            test2       fs-3/fs-y
fs-4    fs-y            test        fs-3/fs-y/fs-4
fs-g    fs-3            test3       fs-3/fs-g
fs-2    fs-g            test        fs-3/fs-g/fs-2
fs-s    fs-2            test        fs-3/fs-g/fs-2/fs-s

Answer 1

由于使用想要避免存储函数，所以需要使用名称而不是ids重新构建路径，使用MySQL 8.0递归查询。

WITH RECURSIVE cte AS (
  SELECT f.id, f.parent_id, f.name, f.fullpath
       , CAST(f.name AS CHAR(50)) AS namepath
    FROM folders f
   WHERE f.id = 'fs-3'
  UNION ALL
  SELECT f.id, f.parent_id, f.name, f.fullpath
       , CONCAT(cte.namepath, '/', f.name) AS namepath
    FROM cte
    JOIN folders f ON f.parent_id = cte.id
)
SELECT id, parent_id, name, fullpath
  FROM cte
 ORDER BY namepath

有关演示，请参见DB Fiddle。

Answer 2

如果MySql 8.0或更高，则为：

WITH RECURSIVE folders_path (id, name, path) AS
(
  SELECT id, name, name as path
    FROM folders
    WHERE parent_id IS NULL
  UNION ALL
  SELECT f.id, f.name, CONCAT(fp.path, '/', f.name)
    FROM folders_path fp JOIN folders AS f
      ON fp.id = f.parent_id
)
SELECT f.id, f.parent_id, f.name, fp.path 
    FROM folders f JOIN folders_path fp on f.id = fp.id
    WHERE fullpath LIKE CONCAT("%", 'fs-3', "%")
    ORDER BY fp.path;   

见DB Fiddle

注意:在上面的did中，我没有费心添加fullpath列，因为添加的示例数据已经满足了WHERE子句。

Answer 3

您可以使用条件排序来完成这一任务：

select f.* 
from folders f
where fullpath like concat("%", 'fs-3', "%")
order by
  case 
    when exists (
      select 1 from folders  
      where name = 'test2'
      and f.fullpath like concat(fullpath, '%')
    ) then 1
    when exists (
      select 1 from folders  
      where name = 'test3'
      and f.fullpath like concat(fullpath, '%')
    ) then 2
  end,
  f.fullpath,
  f.name

见演示。

结果：

| id   | parent_id | name  | fullpath            |
| ---- | --------- | ----- | ------------------- |
| fs-3 | null      | root  | fs-3                |
| fs-d | fs-3      | test  | fs-3/fs-d           |
| fs-5 | fs-d      | test  | fs-3/fs-d/fs-5      |
| fs-y | fs-3      | test2 | fs-3/fs-y           |
| fs-4 | fs-y      | test  | fs-3/fs-y/fs-4      |
| fs-g | fs-3      | test3 | fs-3/fs-g           |
| fs-2 | fs-g      | test  | fs-3/fs-g/fs-2      |
| fs-s | fs-2      | test  | fs-3/fs-g/fs-2/fs-s |