Mysql分组编号、错位、错位叠加

数据准备

-- 创建表(如果存在先删除再创建)
DROP TABLE IF EXISTS readcounts;
CREATE TABLE `readcounts`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `count` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
​
-- 创建造数据存储过程(如果存在先删除再创建)
-- 注：count为0-10之间的随机数
DROP PROCEDURE IF EXISTS caeatereadcountsdata;
CREATE PROCEDURE caeatereadcountsdata()
BEGIN
SET @num = 0;
WHILE
    @num < 100 DO
  INSERT INTO readcounts(type,count) VALUES(CONCAT('test',round(@num/10)),rand()*10);
    SET @num = @num + 1;
END WHILE;
END;
​
-- 执行存储过程
CALL caeatereadcountsdata();

分组编号

思路：通过增加两个变量（@r:行号，@groupkey:分组的key）,如果key一样则行号加1，分组不一样则重置行号

SELECT
    re.*,
    @r := CASE WHEN @rowkey = re.type THEN @r +1 ELSE 1 END AS `serialnumber`,
    @rowkey := re.type as rowKey
FROM
( 
    SELECT 
        type, 
        count 
    FROM readcounts 
    ORDER BY type 
) AS re,(SELECT @r := 1, @rowkey := '' ) AS cus;

错位（错位后的数字等于上一行的count字段的值）

思路：有了上面分组编号的基础，现在需要三个变量（一个存储当前行的分组key，一个存储错位后的数字，一个存储上一行的数字），每次判断上一行的key等不等于当前行的key，如果相等则错位后的数字等于上一行的，否则为0

SELECT
    re.*,
    @newcnt := CASE WHEN @rowkey = re.type THEN  @oldcnt ELSE 0 END AS `newcnt`,
    @rowkey := re.type as rowKey,
    @oldcnt := re.count
FROM
( 
    SELECT 
        type, 
        count 
    FROM readcounts 
    ORDER BY type 
) AS re,(SELECT @newcnt := 0, @rowkey := '' ,@oldcnt := 0) AS cus;

注：讲这一步是为了讲后面的错位叠加，所以8丢失了不用在意，现实中错位这种需求应该少有

错位叠加

思路：有了上面分组编号的基础，现在需要两个变量（一个存储当前行的分组key，一个存储错位后的数字），每次判断上一行的key等不等于当前行的key，如果相等则错位后的数字等于上一行错位后的count的加上当前行count的，否则为当前行count

SELECT
    re.*,
    @newcnt := CASE WHEN @rowkey = re.type THEN  @newcnt + re.count ELSE re.count END AS `newcnt`,
    @rowkey := re.type AS rowKey
FROM
( 
    SELECT 
        type, 
        count 
    FROM readcounts 
    ORDER BY type 
) AS re,(SELECT @newcnt := 0, @rowkey := '') AS cus;