在一系列数字中查找遗漏的最大值和最小值

Question

例如，我有一个数字序列：｛1，2，5，7｝。我需要找到这个序列中遗漏的最小和最大的一个(对于这个例子，min=3和max=6(。值也可以是负数。这是我的解决方案，但它不会传递额外的检查数据库(错误的记录数(减去1((，所以我不能说到底错在哪里。我还尝试了使用LEFTOUTERJOIN和EXCEPT谓词的版本——同样的问题。请帮助我改进我的解决方案。

WITH AA AS (SELECT MAX(Q_ID) MX
FROM UTQ),

BB AS (SELECT MIN(Q_ID) CODE
FROM UTQ

UNION ALL

SELECT CODE + 1
FROM BB
WHERE CODE < (SELECT MX
FROM AA)
)
SELECT MIN(CODE) MIN_RES, MAX(CODE) MAX_RES
FROM BB
WHERE CODE NOT IN (SELECT Q_ID
FROM UTQ)

Answer 1

一种方法是not exists:

select min(q_id + 1)
from utq
where not exists (select 1 from utq utq2 where utq2.q_id = utq.id + 1)
union all
select max(q_id - 1)
from utq
where not exists (select 1 from utq utq2 where utq2.q_id = utq.id - 1);

您也可以使用lead()和lag():

select min(case when next_q_id <> q_id + 1 then q_id + 1 end),
max(case when prev_q_id <> q_id - 1 then q_id - 1 end)
from (select utq.*,
lag(q_id) over (order by q_id) as prev_q_id,
lead(q_id) over (order by q_id) as next_q_id
from utq
) utq;

Answer 2

在这里，基于计数的方法似乎是一个不错的方法。尤其是在序列很大的情况下。

第一个CTE总结了测试表中的最大和最小q_id。第二CTE通过生成最小和最大q_id值之间的完整序列(使用fnNumbers tvf(并将WHERE NOT EXISTS与原始序列进行比较来选择缺失的整数。像这样的东西。

数字函数

create function [dbo].[fnNumbers](
@zero_or_one   bit, 
@n             bigint)
returns table with schemabinding as return
with n(n) as (select null from (values (1),(2),(3),(4)) n(n))
select 0 n where @zero_or_one = 0
union all
select top(@n) row_number() over(order by (select null)) n
from n na, n nb, n nc, n nd, n ne, n nf, n ng, n nh,
n ni, n nj, n nk, n nl, n nm, n np, n nq, n nr;

数据和查询

drop table if exists #seq;
go
create table #seq(
q_id          int unique not null);
insert #seq values (1),(2),(5),(7);
with
max_min_cte(max_q, min_q) as (
select max(q_id), min(q_id)
from #seq),
missing_cte(q_id) as (
select mm.min_q+fn.n
from max_min_cte mm
cross apply dbo.fnNumbers(0, mm.max_q-mm.min_q) fn
where not exists (select 1
from #seq s
where (mm.min_q+fn.n)=s.q_id))
select max(q_id) max_missing, min(q_id) min_missing
from missing_cte;

输出

max_missing min_missing
6           3

Answer 3

您可以使用LEAD尝试以下操作

SELECT MIN(Q_ID + 1) AS MinValue
,MAX(Q_ID + 1) AS MaxValue
FROM (
SELECT *,LEAD(Q_ID) OVER (ORDER BY Q_ID) NQ_ID
FROM (VALUES (1),(2),(5),(7)) v(Q_ID)
) t
WHERE NQ_ID - Q_ID <> 1