工作遇到的给sql行记录编号问题

前言

最近工作遇到一个需求，定时任务每次查询detail表中100张单据（bill），每张单据的明细记录数量都不一致。

方案一

先查询bill主表前50条单据的billNo，拿billNo匹配detail子表的数据。

SELECT * FROM tbl_chain_bill_detail WHERE billNo in (SELECT billNo FROM tbl_chain_bill_detail group by billNo LIMIT 0, 50)

查询报错：

错误信息.png

limit执行顺序靠后，子查询需要在包装一层。

SELECT * FROM tbl_chain_bill_detail WHERE billNo in (SELECT t.billNo FROM (SELECT billNo FROM tbl_chain_bill_detail group by billNo LIMIT 0, 50) t)

执行成功，但需要两个子查询，不喜欢这个方式。

执行计划.png

方案二

SELECT
    ttt.*,@rank := 0 //锚点A
FROM
    (
        SELECT
            (
                CASE   //锚点B
                WHEN @billNo = t.billNo THEN
                    @rank := @rank
                ELSE
                    @rank := IFNULL(@rank, 0) + 1
                END
            ) AS rank,
            @billNo := t.billNo,  //锚点C
            t.*
        FROM
            tbl_chain_bill_detail t
        ORDER BY
            t.billNo DESC  //锚点D
    ) ttt
WHERE
    ttt.rank <= 500;  //锚点E

思路：
mysql可以定义全局变量，生命周期为整个回话。一个记录每行的billNo，一个作为编号。

• 锚点D： 首先把detail表按照billNo排序
• 锚点C： 定义全局变量@billNo，保存每次扫描行记录的billNo，每次扫描行记录@billNo都可能会变化
• 锚点A： 定义全局变量@rank := 0，每次执行sql @rank都会初始化为0
• 锚点B： 判断billNo是否等于上一条记录的billNo，如果相等@rank保持不变，否则@rank + 1
• 锚点E： 对每行记录编好号的sql加记录数限制即可

效果.png

这条sql子查询只有一个，执行计划完爆上面的sql，完美~！

执行计划.png