MySQL实现Oracle的rank over(partition by...order by)叠加start with...connect by...prior...函数

建表SQL

SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS `tb_tags`;
CREATE TABLE `tb_tags` (
  `bid` tinyint(1) unsigned NOT NULL AUTO_INCREMENT,
  `pid` tinyint(1) NOT NULL DEFAULT '0',
  `label` varchar(32) COLLATE utf8_bin NOT NULL,
  `hits` tinyint(1) NOT NULL,
  `weight` tinyint(1) NOT NULL,
  PRIMARY KEY (`bid`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

组内排序

IF (
    @lev = rc.grade,
    @rank := @rank + 1,
    @rank := 1
)

分层级联查询

SELECT
    id_grp.grade,
    b.bid,
    b.label,
    b.hits,
    b.weight
FROM
    (
SELECT
    @ids AS _ids,
    (
SELECT
    @ids := GROUP_CONCAT(bid)
FROM
    tb_tags
WHERE
    FIND_IN_SET(pid, @ids)
    ) AS cids,
    @l := @l + 1 AS grade
FROM
    tb_tags,
    (SELECT @ids := 2, @l := 0) b
WHERE
    @ids IS NOT NULL
    ) id_grp,
    tb_tags b
WHERE
    FIND_IN_SET(b.bid, id_grp._ids)
ORDER BY
    grade,
    bid

 

完整的查询SQL

SELECT
    rc.bid,
    rc.label,
    rc.hits,
    rc.weight,
IF (
    @lev = rc.grade,
    @rank := @rank + 1,
    @rank := 1
) AS rank,
 (@lev := rc.grade) AS grade
FROM
    (
        SELECT
            id_grp.grade,
            b.bid,
            b.label,
            b.hits,
            b.weight
        FROM
            (
                SELECT
                    @ids AS _ids,
                    (
                        SELECT
                            @ids := GROUP_CONCAT(bid)
                        FROM
                            tb_tags
                        WHERE
                            FIND_IN_SET(pid, @ids)
                    ) AS cids,
                    @l := @l + 1 AS grade
                FROM
                    tb_tags,
                    (SELECT @ids := 2, @l := 0) b
                WHERE
                    @ids IS NOT NULL
            ) id_grp,
            tb_tags b
        WHERE
            FIND_IN_SET(b.bid, id_grp._ids)
        ORDER BY
            grade,
            bid
    ) rc,
    (SELECT @rank := 0, @lev := NULL) rd
ORDER BY
  grade,
    rc.hits DESC,
    rc.weight DESC

 

结果如图

 

参考
https://www.cnblogs.com/zhangbojiangfeng/p/6423152.html