查找字母数字序列中缺少的值

2wnc66cl 于 2021-06-18 发布在 Mysql

关注(0)|答案(1)|浏览(272)

我想识别字母数字序列中缺少的值。
该表定义如下：

CREATE TABLE `seqtest` (
  `ID` int(11) NOT NULL,
  `PoleNo` text,
  `Pre` char(1) DEFAULT NULL,
  `Num` int(3) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

数据如下所示，始终为一个字母（a-z），后跟从000到999的三个数字。

| PoleNo |  Pre  |  Num  |
|------------------------|
| A000   |   A   |  000  |
| A001   |   A   |  001  |
| A002   |   A   |  002  |
| A004   |   A   |  003  |
|**** |   *   |***|
| A998   |   A   |  998  |
| A999   |   A   |  999  |
| B000   |   B   |  000  |
| B001   |   B   |  001  |
| B002   |   B   |  002  |
|**** |   *   |***|
| B998   |   B   |  998  |
| B999   |   B   |  999  |
| C000   |   C   |  000  |
| C001   |   C   |  001  |
| C005   |   C   |  005  |
| C006   |   C   |  006  |
|------------------------|

我希望查询发现，例如，c002、c003和c004丢失，如下所示。

|  Pre  | start | stop |
|   C   |   2   |   4  |
|----------------------|

我正在使用以下命令：

SELECT l.Pre, l.Num + 1 as start, min(fr.Num) - 1 as stop
FROM seqtest as l
    LEFT OUTER JOIN seqtest as r ON l.Num = r.Num - 1 AND l.Pre = r.Pre
    LEFT OUTER JOIN seqtest as fr ON l.Num < fr.Num AND l.Pre = fr.Pre
WHERE r.Num is null AND l.Num < 999
GROUP BY l.Pre, l.Num, r.Num

基于此。
它给我的范围，是失踪的，工作良好，除了一个案件…当'前'从一个字母的变化到下一个。
ie具有以下数据：

| PoleNo |  Pre  |  Num  |
|------------------------|
| B995   |   B   |  995  |
| B996   |   B   |  996  |
| B997   |   B   |  997  |
| C003   |   C   |  003  |
| C004   |   C   |  004  |
| C005   |   C   |  005  |
| C006   |   C   |  006  |
|------------------------|

我想把这个还给你：

|  Pre  | start | stop |
|   B   |  998  |  999 |
|   C   |   0   |   2  |
|----------------------|

这可能吗？我正在使用 Pre 以及 Num 只是 PoleNo 但如果有人发现了一种方法 PoleNo 菲尔德，那也行。

sql mysql

来源：https://stackoverflow.com/questions/53291650/find-missing-values-in-an-alphanumeric-sequence

1条答案

按热度按时间

vof42yt11#

在mysql 8+中这要容易得多，因为 lead() . 但是，你可以做你想做的：

select st.pre,
       (st.num + 1) as start,
       (st.next_num - 1) as stop
from (select st.*,
             (select st2.num
              from seqtest st2
              where st2.pre = st.pre and
                    st2.num > st.num
              order by st2.num asc
              limit 1
             ) as next_num
      from seqtest st
     ) st
where next_num <> num + 1;

编辑：
这将获得开头和结尾的范围：

select st.pre, 
       (st.num + 1) as start,
       (st.next_num - 1) as stop
from (select st.pre, num,
             coalesce( (select st2.num
                        from seqtest st2
                        where st2.pre = st.pre and
                              st2.num > st.num
                        order by st2.num asc
                        limit 1), 1000
                      ) as next_num
      from seqtest st
      union
      select st.pre, 0 as num, min(st.num) as next_num
      from seqtest st
      group by st.pre
     ) st
where next_num <> num + 1
order by pre, start;

这是一把小提琴。

赞(0）回复(0）举报 2021-06-18

我来回答

查找字母数字序列中缺少的值

1条答案

相关问题

热门标签

最新问答