有没有一种方法可以查询表中缺失的数字?
问题描述:
我为一家物流公司工作,我们必须为每件运费按照预定顺序提供一个7位数的专业编号。所以我们知道这些数字存在差距,但有什么方法可以查询系统并找出缺少的东西?有没有一种方法可以查询表中缺失的数字?
因此,向我展示列名trace_number中不存在的所有从1000000到2000000的数字。
所以,你可以看到下面的顺序去1024397,1024398,然后1051152,所以我知道有一个26k专业人数的巨大差距,但是有无论如何只是查询差距?
Select t.trace_number,
integer(trace_number) as number,
ISNUMERIC(trace_number) as check
from trace as t
left join tlorder as tl on t.detail_number = tl.detail_line_id
where left(t.trace_number,1) in ('0','1','2','3','4','5','6','7','8','9')
and date(pick_up_by) >= current_date - 1 years
and length(t.trace_number) = 7
and t.trace_type = '2'
and site_id in ('SITE5','SITE9','SITE10')
and ISNUMERIC(trace_number) = 'True'
order by 2
fetch first 10000 rows only
答
我不知道你的查询有问题的事,但你可以使用lag()
/lead()
找出差距。这个想法是:
select (trace_number + 1) as start_gap,
(next_tn - 1) as end_gap
from (select t.*,
lead(trace_number) order by (trace_number) as next_tn
from t
) t
where next_tn <> trace_number + 1;
这在一个范围内没有找到它们。它只是找到所有的差距。
+0
我也可能采取这种方法 –
答
尝试这样的事情(适应这里的条件,投入“的”条款):
with Range (nb) as (
values 1000000
union all
select nb+1 from Range
where nb<=2000000
)
select *
from range f1 left outer join trace f2
on f2.trace_number=f1.nb
and f2.trace_number between 1000000 and 2000000
where f2.trace_number is null
不漂亮的答案,所以我会离开它作为评论...创建一个临时表和填充它与从1000000到2000000的每个数字(使用一个简单的循环来填充)。将此临时表和左连接跟踪,其中trace.tracenumber为空将返回所有“未使用”的数字。 – Twelfth
在PostgreSQL中,我认为你可以使用'SELECT t.trace_number + 1 FROM trace t WHERE NOT EXISTS(SELECT 1 FROM trace AS t2 WHERE t2.trace_number = t.trace_number + 1)'来获得每个间隙范围的起始点;据推测DB2允许类似的东西。类似的查询可能会得到终点。然后,您可以'UNION'这两个查询并按'trace_number'排序,以获得奇数行上的空位起始位置和偶数行上的空位终止位置。 –
很常见的问题,谷歌* SQL的差距和岛屿* – Charles