我有以下查询

SELECT process.*
FROM (`process`)
WHERE `company_id` = '1' AND `status` = '1'                                                        
ORDER BY COALESCE(`process`.`parent_id`, `process`.`id`), `process`.`parent_id` IS NOT NULL asc, `process`.`position` asc

表、数据、查询见SQLFiddie

http://sqlfiddle.com/#!9/d50ba/2

查询根据其位置编号对子流程进行正确排序。 但是，我还需要按位置编号(均为 ASC)排序的主进程。

编辑 订单操作 IDS 应为 7、1、16、41、42、6、40 它应该首先按 ASC 对位置上的所有主进程进行排序，并在每个主进程内对位置 ASC 上的所有子进程进行排序

它是:

╔═════╦════════════╦═════════════╦════════════════╦═══════════════════╦═════════════╦═══════════════╦═══════════╦═════════════╦═══════════╦═════════╦════════════════════════════╗
║ id  ║ parent_id  ║ company_id  ║ department_id  ║       name        ║ user_score  ║ user_comment  ║ ic_score  ║ ic_comment  ║ position  ║ status  ║        date_created        ║
╠═════╬════════════╬═════════════╬════════════════╬═══════════════════╬═════════════╬═══════════════╬═══════════╬═════════════╬═══════════╬═════════╬════════════════════════════╣
║  1  ║ (null)     ║          1  ║             3  ║ Main Process 1    ║          0  ║ (null)        ║        0  ║ (null)      ║        2  ║      1  ║ February, 02 2015 21:04:03 ║
║ 16  ║ 1          ║          1  ║             3  ║ Sub Process 1, 1  ║          0  ║ (null)        ║        0  ║ (null)      ║        1  ║      1  ║ February, 02 2015 21:14:34 ║
║ 41  ║ 1          ║          1  ║             3  ║ Sub Process 4, 1  ║          0  ║ (null)        ║        0  ║ (null)      ║        9  ║      1  ║ February, 09 2015 10:06:40 ║
║  6  ║ (null)     ║          1  ║             3  ║ Main Process 2    ║          0  ║ (null)        ║        0  ║ (null)      ║        8  ║      1  ║ February, 02 2015 21:08:53 ║
║  7  ║ (null)     ║          1  ║             3  ║ Main Process 3    ║          0  ║ (null)        ║        0  ║ (null)      ║        1  ║      1  ║ February, 02 2015 21:09:08 ║
║ 40  ║ (null)     ║          1  ║             3  ║ Main Process 2    ║          0  ║ (null)        ║        0  ║ (null)      ║       16  ║      1  ║ February, 09 2015 10:05:36 ║
║ 42  ║ (null)     ║          1  ║             3  ║ Main Process 3    ║          0  ║ (null)        ║        0  ║ (null)      ║        7  ║      1  ║ February, 09 2015 10:14:36 ║
╚═════╩════════════╩═════════════╩════════════════╩═══════════════════╩═════════════╩═══════════════╩═══════════╩═════════════╩═══════════╩═════════╩════════════════════════════╝

期望的输出:

╔═════╦════════════╦═════════════╦════════════════╦═══════════════════╦═════════════╦═══════════════╦═══════════╦═════════════╦═══════════╦═════════╦════════════════════════════╗
║ id  ║ parent_id  ║ company_id  ║ department_id  ║       name        ║ user_score  ║ user_comment  ║ ic_score  ║ ic_comment  ║ position  ║ status  ║        date_created        ║
╠═════╬════════════╬═════════════╬════════════════╬═══════════════════╬═════════════╬═══════════════╬═══════════╬═════════════╬═══════════╬═════════╬════════════════════════════╣
║  7  ║ (null)     ║          1  ║             3  ║ Main Process 3    ║          0  ║ (null)        ║        0  ║ (null)      ║        1  ║      1  ║ February, 02 2015 21:09:08 ║
║  1  ║ (null)     ║          1  ║             3  ║ Main Process 1    ║          0  ║ (null)        ║        0  ║ (null)      ║        2  ║      1  ║ February, 02 2015 21:04:03 ║
║ 16  ║ 1          ║          1  ║             3  ║ Sub Process 1, 1  ║          0  ║ (null)        ║        0  ║ (null)      ║        1  ║      1  ║ February, 02 2015 21:14:34 ║
║ 41  ║ 1          ║          1  ║             3  ║ Sub Process 4, 1  ║          0  ║ (null)        ║        0  ║ (null)      ║        9  ║      1  ║ February, 09 2015 10:06:40 ║
║ 42  ║ (null)     ║          1  ║             3  ║ Main Process 3    ║          0  ║ (null)        ║        0  ║ (null)      ║        7  ║      1  ║ February, 09 2015 10:14:36 ║
║  6  ║ (null)     ║          1  ║             3  ║ Main Process 2    ║          0  ║ (null)        ║        0  ║ (null)      ║        8  ║      1  ║ February, 02 2015 21:08:53 ║
║ 40  ║ (null)     ║          1  ║             3  ║ Main Process 2    ║          0  ║ (null)        ║        0  ║ (null)      ║       16  ║      1  ║ February, 09 2015 10:05:36 ║
╚═════╩════════════╩═════════════╩════════════════╩═══════════════════╩═════════════╩═══════════════╩═══════════╩═════════════╩═══════════╩═════════╩════════════════════════════╝

最佳答案

这是可能的，但是您需要父进程在子进程行中的位置。为此，您需要自行加入流程表。为了实现所需的排序，我使用了位移位。这将产生一个 BIGINT，其中前 32 位反转用于父位置，后 32 位保留用于子位置。在父行的情况下，它们自己的位置用作前 32 位的位置，后 32 位设置为 0。

这会产生以下查询:

SELECT `process`.*
FROM `process`
LEFT JOIN `process` AS `processParent` ON `processParent`.`id` = `process`.`parent_id`
WHERE `process`.`company_id` = '1' AND `process`.`status` = '1'                                                        
ORDER BY 
    (IF(`process`.`parent_id` IS NULL, `process`.`position`, `processParent`.`position`) << 32)
    + IF(`process`.`parent_id` IS NULL, 0, `process`.`position`) ASC;

编辑:请记住，这仅在进程是父进程或子进程时才有效，但不能同时是两者。

关于MySQL - 按父位置和子位置排序，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/32884835/