MySQL选择分组整数的范围

Question

我在数据库中存储了一些客户端号码、分配的 ip、routerIP、vlan 和区域

   No    |       ip         |      router     |   idZone   |   vlan  | 
---------------------------------------------------------------------
   9600  |   10.95.65.100   |   10.10.10.26   |     2      |   105   |
   9601  |   10.95.65.101   |   10.10.10.26   |     2      |   105   |
   9602  |   10.95.65.102   |   10.10.10.27   |     2      |   107   |
  16090  |    10.32.5.90    |   10.10.10.25   |     1      |   103   |
  16091  |    10.32.5.91    |   10.10.10.25   |     1      |   103   |
  16092  |    10.32.5.92    |   10.10.10.25   |     1      |   103   |
  16093  |    10.32.5.93    |   10.10.10.25   |     1      |   103   |
  16095  |    10.32.5.95    |   10.10.10.25   |     1      |   103   |
  20100  |   10.20.10.200   |   10.10.10.32   |     3      |   109   |

我需要按路由器、区域和 vlan 分组显示哪些客户端号码已在使用中，以获得如下信息:

 from  |    to   |     router     |   zone   |  vlan
-----------------------------------------------------
 9600  |   9601  |  10.10.10.26   |     2    |   105   |
 9602  |   9602  |  10.10.10.27   |     2    |   107   |
16090  |  16093  |  10.10.10.25   |     1    |   103   |
16095  |  16095  |  10.10.10.25   |     1    |   103   |
20100  |  20100  |  10.10.10.32   |     3    |   109   |

客户编号是唯一的，现在我的查询是这样的:

SELECT
    MIN( no ) AS start_no,
    MAX( no ) AS end_no,
    router,
    idZone,
    vlan
FROM
    address
GROUP BY
    router,
    idZone,
    vlan 

但是该查询没有考虑第一个和最后一个之间的缺失点，例如而不是

 from  |    to   |     router     |   zone   |  vlan
--------------------------------------------------------
16090  |  16093  |  10.10.10.25   |     1    |   103   |
16095  |  16095  |  10.10.10.25   |     1    |   103   |

我愿意

 from  |    to   |     router     |   zone   |  vlan
-----------------------------------------------------
16090  |  16095  |  10.10.10.25   |     1    |   103   |

这是错误的，因为数据库中不存在记录 16094

建议？

谢谢!

Answer 1

以下查询适用于所有情况:

(SELECT
    MIN( no ) AS start_no,
    MAX( no ) AS end_no,
    router,
    idZone,
    vlan
FROM
    address a1
    where exists (SELECT a2.no FROM address a2 WHERE a2.no = a1.no + 1) or
          exists (SELECT a2.no FROM address a2 WHERE a2.no = a1.no - 1)
GROUP BY
    router,
    idZone,
    vlan 
)
union
(select no as_no, no as end_no, router,idzone,vlan
from address a1
where not exists (SELECT a2.no FROM address a2 WHERE a2.no = a1.no + 1) and
      not exists (SELECT a2.no FROM address a2 WHERE a2.no = a1.no - 1)
GROUP BY
    router,
    idZone,
    vlan 
 )
   ORDER BY vlan

DEMO

union 之前的查询部分将返回所有具有连续client no 的组，而查询的另一部分将返回其他单个组。