问题描述
考虑具有列序列号的表productSerialnumber.该列采用产品的单个序列号.
Consider a table productSerialnumber with a column serialnumber. this column takes a single serial number of a Product.
产品A的序列号从101到109,然后是111到119,然后是139到150.例如,序列号110和120-138都不可用
Product A has a serial number from 101 to 109 and then 111 to 119 and 139 to 150. the serialnumber 110 and 120-138 for example are not availabel
我想查询或设置可以返回连续序列号的东西.例如结果将是
I would like to have a query or something that can return continous serialnumber set.for example the result will be
from to
======= ====
101 109
111 119
139 150
要考虑的是,表包含的数据量超过一百万行.
to be considered is that table has a huge set of data more then a million rows.
任何帮助都会得到真正的帮助
Any help will be really appriciated
推荐答案
更多内容:-
SELECT MIN(aFirstSerial), MAX(aLastSerial)
FROM
(
SELECT @FirstSerial:=IF(productSerialnumber = @LastSerial + 1, IF(@FirstSerial = 0, productSerialnumber, @FirstSerial), productSerialnumber) AS aFirstSerial,
@RangeNum:=IF(productSerialnumber = @LastSerial + 1, @RangeNum, @RangeNum + 1) AS aRangeNum,
@LastSerial := productSerialnumber AS aLastSerial
FROM
(
SELECT productSerialnumber
FROM Product
ORDER BY productSerialnumber
) Sub1
CROSS JOIN (SELECT @PrevSerial:=0, @RangeNum:=0, @FirstSerial:=0, @LastSerial:=0) Sub2
) Sub3
GROUP BY aRangeNum
SQL Fiddle在这里:-
SQL Fiddle for it here:-
http://sqlfiddle.com/#!2/5cbc2/12
这篇关于如何找到无间隙的mysql序列号?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!