mysql - 如何忽略下一个重复行？

Question

我需要你的帮助!我有一张 table :

CREATE TABLE `table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `res` varchar(255) DEFAULT NULL,
  `value` int(6) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

-- Records of table

INSERT INTO `table` VALUES (1, 'gold', 44);
INSERT INTO `table` VALUES (2, 'gold', 44);
INSERT INTO `table` VALUES (3, 'gold', 45);
INSERT INTO `table` VALUES (4, 'gold', 46);
INSERT INTO `table` VALUES (5, 'gold', 44);
INSERT INTO `table` VALUES (6, 'gold', 44);
INSERT INTO `table` VALUES (7, 'gold', 44);
INSERT INTO `table` VALUES (8, 'gold', 47);

我需要发出 SELECT 请求，该请求将忽略下一个或上一个重复行，并且我收到如下数据:

 - gold:44 (ignored 1 record)
 - gold:45 
 - gold:46 
 - gold:44 (ignored 2 records)
 - gold:47

没有重复记录会忽略的对象(第一个、第二个、最后一个)。(我尝试使用按值分组或不同，但这种方式会删除具有相同值的其他记录)

Answer 1

您可以使用间隙和孤岛解决方案来解决此问题。
- 通常涉及 MySQL 中不存在的 ROW_NUMBER()
- 下面的解决方案使用变量和 ORDER BY 模仿 ROW_NUMBER()

链接到示例:http://sqlfiddle.com/#!9/32e72/12

SELECT
    MIN(id)   AS id,
    res,
    value
FROM
(
    SELECT
        IF (@res = res AND @val = value, @row := @row + 1, @row := 1)     AS val_ordinal,
        id              AS id,
        res_ordinal     AS res_ordinal,
        @res := res     AS res,
        @val := value   AS value
    FROM
    (
        SELECT
            IF (@res = res                 , @row := @row + 1, @row := 1)     AS res_ordinal,
            id              AS id,
            @res := res     AS res,
            @val := value   AS value
        FROM
            `table`,
        (
            SELECT @row := 0, @res := '', @val := 0
        )
            AS initialiser
        ORDER BY
            res, id
    )
        AS sequenced_res_id,
    (
        SELECT @row := 0, @res := '', @val := 0
    )
        AS initialiser
    ORDER BY
        res, value, id
)
    AS sequenced_res_val_id
GROUP BY
    res,
    value,
    res_ordinal - val_ordinal
ORDER BY
    MIN(id)
;

如果我将 res_ordinal、val_ordinal 和 res_ordinal - val_ordinal 添加到您的数据中，可以看出您现在可以区分这两者组 44

                                                              GROUP

INSERT INTO `table` VALUES ('1', 'gold', '44');   1 - 1 = 0   (Gold, 44, 0)
INSERT INTO `table` VALUES ('2', 'gold', '44');   2 - 2 = 0

INSERT INTO `table` VALUES ('3', 'gold', '45');   3 - 1 = 2   (Gold, 45, 2)

INSERT INTO `table` VALUES ('4', 'gold', '46');   4 - 1 = 3   (Gold, 46, 3)

INSERT INTO `table` VALUES ('5', 'gold', '44');   5 - 3 = 2   (Gold, 44, 2)
INSERT INTO `table` VALUES ('6', 'gold', '44');   6 - 4 = 2
INSERT INTO `table` VALUES ('7', 'gold', '44');   7 - 5 = 2

INSERT INTO `table` VALUES ('8', 'gold', '47');   8 - 1 = 7   (Gold, 47, 7)

注意:根据您的数据，我可以使用 id 而不是制作自己的 res_ordinal。然而，这样做可以解决 id 序列中的间隙以及拥有多个不同资源的问题。这意味着在下面的示例中，两 block 金牌被认为是彼此的重复项...

1   Gold    44     1 - 1 = 0   (Gold, 44, 0)
2   Poop    45     1 - 1 = 0   (Poop, 45, 0)
3   Gold    44     2 - 2 = 0   (Gold, 44, 0)  -- Duplicate
4   Gold    45     3 - 1 = 2   (Gold, 44, 2)