在表格中查找连续空闲的数字

Question

在表格中查找连续空闲的数字

5

我有一个表格，其中包含数字（电话号码）和代码（免费或不可用）。

现在，我需要找到一系列连续的30个数字，例如079xxx100-079xxx130，并且所有这些数字都必须具有免费状态。

以下是我的表格外观示例：

CREATE TABLE numere
(
    value int,
    code varchar(10)
);


INSERT INTO numere (value,code)
Values
 (123100, 'free'),
 (123101, 'free'),
...
 (123107, 'booked'),
 (123108, 'free'),
 (...
 (123130, 'free'),
 (123131, 'free'),
 ...

 (123200, 'free'),
 (123201, 'free'),
...
 (123230, 'free'),
 (123231, 'free'),
 ...

我需要一条SQL查询语句，在这个例子中，获取123200-123230范围内的内容（以及所有下一个可用的范围）。

现在，我找到了一个例子，基本上做到了我所需要的：

select value, code
from numere
where value >= (select a.value
                from numere a
                left join numere b on a.value < b.value 
                                   and b.value < a.value + 30 
                                   and b.code = 'free'
                where a.code = 'free'
                group by a.value
                having count(b.value) + 1 = 30)
limit 30

但是这只返回了前30个可用数字，而不是在我的范围内（0-30）。（而且需要13分钟才能执行，呵呵..）

如果有人有想法，请告诉我（我正在使用SQL Server）

- Andrian Nani

4个回答

1

这是常见的岛屿和间隔问题。

; with cte as
( 
    select  *, grp  = row_number() over (order by value)
            - row_number() over (partition by code order by value)
    from    numere
),
grp as
(
    select  grp
    from    cte
    group by grp 
    having count(*) >= 30
)
select  c.grp, c.value, c.code
from    grp g
    inner join cte c    on  g.grp   = c.grp

- Squirrel

1

您可以使用以下SQL查询表数据以查找预订号码之间的差距，其中使用了SQL LEAD()分析函数。

;with cte as (
 select
  value, lead(value) over (order by value) nextValue
 from numere
 where code = 'booked'
), cte2 as (
select
 value gapstart, nextValue gapend,
 (nextValue - value - 1) [number count in gap] from cte
where value < nextValue - 1 
)
select * 
from cte2
where [number count in gap] >= 30

你可以查看SQL教程使用SQL查找序列中的缺失数字和间隔。

希望对你有所帮助，

- Eralper

1

无法立即测试，但这可能有效：

SELECT a.Value FROM (SELECT Value FROM numere WHERE Code='free' ) a INNER Join (SELECT Value FROM numere WHERE code='free' ) b ON b.Value BETWEEN a.Value+1 AND a.Value+29 GROUP BY a.Value HAVING COUNT(b.Value) >= 29 ORDER BY a.Value ASC

输出结果应该是所有具有接下来29个空闲数字的数字（因此是30个连续数字）。

- quorti

谢谢，这个可以用，只显示值，不显示代码（我对SQL非常新，但我会尝试修改，以便输出两列）。 - Andrian Nani

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- UnhandledExcepSean · Accepted Answer

这个在我的数据集中似乎可以运行。修改选择语句并查看它是否适用于你的表名。

DECLARE @numere TABLE
(
value int,
code varchar(10)
);


INSERT INTO @numere (value,code) SELECT 123100, 'free'

WHILE (SELECT COUNT(*) FROM @numere)<=30
BEGIN
    INSERT INTO @numere (value,code) SELECT MAX(value)+1, 'free' FROM @numere
END

UPDATE @numere
SET code='booked'
WHERE value=123105

select *
from @numere n1
inner join @numere n2 ON n1.value=n2.value-30
    AND n1.code='free'
    AND n2.code='free'
LEFT JOIN @numere n3 ON n3.value>=n1.value
    AND n3.value<=n2.value
    AND n3.code<>'free'
WHERE n3.value IS NULL