时间戳

首页 > 解决方案 > MySQL在两列序列中查找间隙

mysql - MySQL在两列序列中查找间隙

问题描述

我已经搜索并尝试在此处修改其他一些答案以获得解决方案，但我无法提出任何可行的方法。

我有一个有两列序列的表。不幸的是，第一列是一个字符串，它表示一个十六进制值，例如 0x0010。第二列是另一个从 0 到 15 的计数器。

我正在寻找每个十六进制空间中从 0x0000 到 0xFFFF 以及从 0-15 的间隙。一些十六进制空格可以完全为空，在这种情况下，查询或过程将报告所有 0-15 都是空白的。

例如，给定：

id    hex_counter    integer_counter
1          0x0000                  0
2          0x0000                  1
3          0x0000                  5
4          0x0001                 10
5          0x0003                  7

并且只搜索从 0x0 到 0x3，我希望是这样的：

hex_counter    gaps
0x0000         2-4
0x0000         6-15
0x0001         0-9
0x0001         11-15
0x0002         0-15
0x0003         0-6
0x0003         8-15

或同一组中的每个数字在其自己的行中。

我试过把它放在一个存储过程中，如下所示：

CREATE PROCEDURE find_blanks()
BEGIN
    DECLARE curr INT UNSIGNED DEFAULT 0;
    DECLARE max INT UNSIGNED DEFAULT 65535;

    CREATE TEMPORARY TABLE IF NOT EXISTS unused
    (
        hex_counter VARCHAR(255),
        gap VARCHAR(255)
    );

    WHILE curr < max DO
        INSERT INTO unused (hex_counter, gap)
        SELECT CONCAT( '0x', LPAD(CONV(curr, 10, 16), 4, '0')), m.missing from (
        SELECT CONCAT(z.expected, IF(z.got+1<z.expected, CONCAT('-',z.got+1), '')) AS missing FROM ( SELECT @rownuma:=@rownuma-1 AS expected, IF(@rownuma=int_counter, 16, @rownuma:=int_counter) AS got FROM (SELECT @rownuma:=16) AS a JOIN table WHERE hex_counter = CONCAT( '0x', LPAD(CONV(curr, 10, 16), 4, '0')) ORDER BY int_counter) AS z WHERE z.got >= 0 AND z.got < 16
        UNION
        SELECT CONCAT(z.expected, IF(z.got-1>z.expected, CONCAT('-',z.got-1), '')) AS missing FROM ( SELECT @rownumb:=@rownumb+1 AS expected, IF(@rownumb=int_counter, 0, @rownumb:=int_counter) AS got FROM (SELECT @rownumb:=0) AS a JOIN table WHERE project = project_in AND hex_counter = CONCAT( '0x', LPAD(CONV(curr, 10, 16), 4, '0')) ORDER BY int_counter) AS z WHERE z.got >= 0 AND z.got < 16) as m;
        SET curr = curr + 1;
    END WHILE;

    DROP TABLE IF EXISTS unused;

    SELECT * from unused;
END;

这将返回不正确的信息，例如负数等。我即将在 python 中实现一些东西，它将为每个十六进制值发送一个查询，但这将导致大约 65535 个查询，如果可能的话，我想避免这种情况。

谢谢！

标签： mysql

解决方案

仅适用于 8.0 之前的版本...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id SERIAL PRIMARY KEY
,hex_counter CHAR(6) NOT NULL
,integer_counter INT NOT NULL
);

INSERT INTO my_table VALUES
(1,'0x0000', 0),
(2,'0x0000', 1),
(3,'0x0000', 5),
(4,'0x0001',10),
(5,'0x0003', 7);

DROP TABLE IF EXISTS ints;

CREATE TABLE ints (i INT NOT NULL PRIMARY KEY);

INSERT INTO ints VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

SELECT hex_counter
     , MIN(int_counter) range_start
     , MAX(int_counter) range_end 
  FROM 
     ( SELECT hex_counter
            , int_counter
            , CASE WHEN @prev=j THEN @k:=@k ELSE @k:=@k+1 END k
            , @prev:=j b
         FROM 
            ( SELECT UNHEX(HEX('0x0000')+h.i) hex_counter
                   , (i2.i*10)+i1.i int_counter
                   , COALESCE(x.integer_counter = (i2.i*10)+i1.i,0) j
                FROM ints h
                JOIN ints i1
                JOIN ints i2
                LEFT
                JOIN my_table x
                  ON x.hex_counter = UNHEX(HEX('0x0000')+h.i)
                 AND x.integer_counter = (i2.i*10)+i1.i
                JOIN (SELECT @j:=0) vars
               WHERE h.i <=3
                 AND (i2.i*10)+i1.i <= 15
               ORDER 
                  BY hex_counter
                   , int_counter
            ) a
         JOIN (SELECT @prev:=null,@k:=0) vars
        ORDER 
           BY hex_counter
            , int_counter
      ) n
  WHERE b = 0
  GROUP 
     BY hex_counter, k;

+-------------+-------------+-----------+
| hex_counter | range_start | range_end |
+-------------+-------------+-----------+
| 0x0000      |           2 |         4 |
| 0x0000      |           6 |        15 |
| 0x0001      |           0 |         9 |
| 0x0001      |          11 |        15 |
| 0x0002      |           0 |        15 |
| 0x0003      |           0 |         6 |
| 0x0003      |           8 |        15 |
+-------------+-------------+-----------+

推荐阅读