MySQL select range of grouped integers

问题: I have stored in a database some client numbers, the assigned ip, the routerIP, the vlan and the Zone No | ip | router | idZone | vlan...

问题:

I have stored in a database some client numbers, the assigned ip, the routerIP, the vlan and the Zone

   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   |

I need to present which clients numbers are already in use grouped by router, zone and vlan to obtain something like this:

 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   |

the client numbers are unique, right now My Query is something like this:

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

But that query is not considering the missing points between the first and last, for example instead of

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

I would have

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

Which is wrong because the record 16094 is not present in the database

Suggestions?

Thanks!


回答1:

Following query should work for all scenarios:

(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

The part of query before union will return all the groups which has continuous client no & the other part of query will return the other single groups.


回答2:

You could start here:

SELECT no
     , CASE WHEN no = @prev+1 THEN @i:=@i ELSE @i:=@i+1 END i
     , @prev := no
  FROM my_table
     , (SELECT @prev:=null, @i:=0) vars
 ORDER
    BY no;

回答3:

Your query is correct but there might be an empty space in one of the pivot fields making it difficult to group. For example one of the pivot columns in the group by clause say router could be "10.10.10.25" or "10.10.10.25 ". So try to trim the spaces.

SELECT
    MIN( no ) AS `from`,
    MAX( no ) AS `to`,
    trim(router) router,
    trim(idZone) zone,
    trim(vlan) vlan
FROM
    address
GROUP BY
    trim(router),
    trim(idZone),
    trim(vlan);
  • 发表于 2019-02-20 00:12
  • 阅读 ( 189 )
  • 分类:sof

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除