MySQL 5.5.23：如何按规则排序含新/原始收据号的数据

需求说明 #

• 对receipt列进行排序，先按is_modify=0的记录Id升序排列（Id顺序为1→2→4→6→7）
• 若存在og_recept不为空字符串且is_modify=1的记录，需将其放置在关联的原始收据记录下方
• 使用MySQL v5.5.23版本

解决方案 #

由于MySQL 5.5不支持窗口函数，可通过子查询关联+用户变量实现需求：

SELECT 
    t.Id,
    t.receipt,
    t.og_recept,
    t.is_modify,
    @group_num := CASE 
        WHEN t.is_modify = 0 THEN @group_num + 1 
        ELSE @group_num 
    END AS `group`,
    CASE 
        WHEN t.is_modify = 0 THEN 1 
        ELSE 2 
    END AS group_order
FROM (
    -- 关联原始记录与修改记录，按目标规则预排序
    SELECT a.*
    FROM TableA a
    LEFT JOIN TableA b 
        ON a.og_recept = b.receipt 
        AND a.is_modify = 1 
        AND b.is_modify = 0
    ORDER BY 
        COALESCE(b.Id, a.Id),
        a.is_modify
) t
CROSS JOIN (SELECT @group_num := 0) vars
ORDER BY `group`, group_order;

逻辑说明 #

1. 子查询通过自连接，将修改记录（is_modify=1）关联到对应的原始记录（is_modify=0）
2. 预排序时，优先按原始记录的Id升序排列；无原始记录的按自身Id升序，组内原始记录在前、修改记录在后
3. 使用用户变量@group_num为每组分配唯一编号：原始记录触发编号递增，修改记录沿用同一编号
4. group_order直接根据is_modify赋值，原始记录为1，修改记录为2

内容的提问来源于stack exchange，提问作者Jefflee0915